Forum Discussion
Error with formula when moving cells #REF
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!
6 Replies
- 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...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!
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....
