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