Error with formula when moving cells #REF

[LHarris89]

I am doing a cash flow with several tabs and a collections / totals tab which has formulas totalling all the sheets together. If I move a cell I get a REF error in my formula. I have a similar spreadsheet which this doesn't happen on but I can't see what the difference is and why it is not working as it should! It is ok if I copy and paste and then delete the cell contents but it is easier to click and drag where I need it to go as sometimes I may need to move a few cells at a time!

[#2]

I need to question your underlying methodology here: why, once the workbook has been designed are you "manually" moving a cell? The mere fact that you're doing that suggests that your design is inherently unstable and you'd benefit more from a more solid design than just from an answer to your presenting question.
It's as if your mental model is some prior paper process where all was visible and you could copy and erase to reorganize bits and pieces. Excel works better (is more reliable) if your raw data elements are organized and stable and you take advantage of either the database functions (those that begin with D____) or the various VLOOKUP and similar formulas that retrieve data from a table. But you don't want to be moving the raw data around...

[#3]

Hi @mathetes   thank you for your reply! I am not entirely sure if I understand it though!  I am projecting construction costs for the year ahead. Each column is the month and costs are entered into each month. If construction programme slips and the project won't be starting until say 3 months time, all the costs projected need to move from say October to January. In order to do this, I click and drag the cost to where it needs to go. But this seems to be messing up with my formulas. I have another similar sheet that it works ok on. It seems if I am totalling all above ie =sum(e3:e15) this works if things move around but if it is only 1 line to total ie =e3 it is not working! 

[#4]

@LHarris89  Well, you are confirming my suspicion...I'm not going to be available the rest of today; will be tomorrow. But Excel could (I'm quite sure) deal with this more robustly, and even give you a track of the history of such changes, if you started dealing with it in database terms. Is it possible for you to upload to this site a sample of your spreadsheet? That would help me (and others) come up with solutions for you.

My main concern is that if we were to figure out why you're getting that #REF error, but leave you doing things as you are, you'd be in effect putting a bandage on a deeper issue without dealing with that underlying condition.

I envision instead of a single column for each month, a database with multiple columns, with headings possibly along the following lines:  Project, Stage, Cost, EstimatedCompletionDate, RevisedCompletionDate, .....LastRevisionDate....

You could have ALL projects' cash flows, cash in, cash out, dates, etc..... in ONE database, and then extract needed data for reports using Database functions or, possibly, the Pivot Table. It depends on what in/out requirements you're needing to meet. I.e., what your incoming data elements are, what your reporting (outputs) are....

but excel can do wonders with the right design, and you wouldn't need to be manually moving based on a new date of completion; instead, that would be part of the history that you'd be keeping.

I hope that makes at least a little bit of sense. Put up a sample of your sheet...and if you can, copies of the kind of reports you want to generate....

[#5]

Hello @LHarris89,

If you would like to learn why you are getting the #REF! error or how to fix this issue, please refer to the following link:

https://support.office.com/en-us/article/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5f...

[#6]

@mathetes i have attached a very simple example of what i am trying to achieve. Has 2 tabs - consultant and architect costs. On each tab costs are scheduled per job and per month. The collection sheet totals all these costs. I have duplicated this information and then moved some of the costs around and you can see on the totals pages I get REF but in the totals for each month these are ok ie. row 15. If you can suggest a better way of doing this, it would be much appreciated :) 

[#7]

@LHarris89  OK, I've attached a sample of how you could accomplish the same results by modifying the way you enter the information in the first place, and then using a Pivot Table to create the summary report. This uses exactly the data you gave me in your sample. But I think it illustrates what is possible by changing how you envision the process.

Among other things (I've put two extended comments into the workbook), there's only a single database, incorporating job, month, vendor and amount into one database whether it's the architect or the consultant. If you need to separate those out for reporting, there's a way to do that within the Pivot Table process. I'll leave that for now to a later stage, but basically I recommend you find a course (or read up on your own, if you're  comfortable doing that) on the way to create a Pivot Table.....it's a very powerful tool. It does depend on thinking differently about your "raw data" and that I've tried to model for you in the database tab in this revised sample.