I need to prepare report on weekly basis, so I have made a transfer sheet and filled with formulas, whenever I plug the new data in a sheet and in the destination sheet, I have my formulas . But last time when I plugged the data, I deleted the old data and plugged in the new data in the same sheet, my formula started to showing #REF. I had to reenter the formulas, what was the reason? Any thought? What can I do to prevent this mishap in the future?
Charts and formulas
- Thread starter GN0001
- Start date
#REF! occurs for a number of reasons but most commonly when a cell, range, sheet or file that formula are linked to is deleted.
Have you or somebody else deleted any of the above?
Have you deleted a Named Formula?
Prevention:
If multiple people are using the file Lock it to prevent people using areas they should have access to
I would not have any formulas on a transfer sheet. Link to it to retrieve data but don't have formulas on it.
Have you or somebody else deleted any of the above?
Have you deleted a Named Formula?
Prevention:
If multiple people are using the file Lock it to prevent people using areas they should have access to
I would not have any formulas on a transfer sheet. Link to it to retrieve data but don't have formulas on it.
Hi Hui,
Long time and no talk, however you always pass through my mind from time to time.
I am using formulas in this way.
1-I plug in the sheet to my workbook.
2-I have entered formulas into sheet2.
3-when I plug in the sheet which contains values, the result should be shown in the second sheet.
You are saying transfer sheet. which sheet will be the transfer sheet?
I think the reason my formulas got messed up was that I deleted the data and then copied the new data into it, I shouldn't have deleted the data, I should have copied and pasted the new data on top of old one without deleting the old one. This is what I think? Any tips?
How do I need to lock the sheet? Do I need to Lock the whole workbook?
Thank for the help. I am hoping with getting some advise from you I can get my workbook work without having to enter the formulas into it again and being able to come back home from work early.
Long time and no talk, however you always pass through my mind from time to time.
I am using formulas in this way.
1-I plug in the sheet to my workbook.
2-I have entered formulas into sheet2.
3-when I plug in the sheet which contains values, the result should be shown in the second sheet.
You are saying transfer sheet. which sheet will be the transfer sheet?
I think the reason my formulas got messed up was that I deleted the data and then copied the new data into it, I shouldn't have deleted the data, I should have copied and pasted the new data on top of old one without deleting the old one. This is what I think? Any tips?
How do I need to lock the sheet? Do I need to Lock the whole workbook?
Thank for the help. I am hoping with getting some advise from you I can get my workbook work without having to enter the formulas into it again and being able to come back home from work early.
With what you said, You plug in a sheet in and have formulas on another sheet.
That is ok, but if you have moved/copied a sheet into your file a second sheet cannot refer to the new sheet without manual intervention.
Better is to have an Input or Transfer sheet already in place.
Clear the sheet and then paste new data into that sheet.
You need to be sure you are pasting data in in the same format every time.
A second sheet can then pickup the data using formulas and process it as required.
That is ok, but if you have moved/copied a sheet into your file a second sheet cannot refer to the new sheet without manual intervention.
Better is to have an Input or Transfer sheet already in place.
Clear the sheet and then paste new data into that sheet.
You need to be sure you are pasting data in in the same format every time.
A second sheet can then pickup the data using formulas and process it as required.
Hi, I've come across this problem to in the same scenario of running weekly reports where new data needs to be copy-pasted to replace old data. As Hui mentioned the #REF! error occurs mostly when a cell, range, sheet or file that formula are linked to is deleted. Therefore, I found that I had to be sure to "clear content" on the old data to replace it with the new data instead of deleting rows/columns. Pav.