Solved Moving cells in an Excel 2007 formula results in #REF issue.

September 18, 2012 at 06:41:09
Specs: Windows 7
Hi,
I'm having an issue with Excel. I am using Windows 7 (don't know if this matters) and Excel 2007. I recently changed to a new laptop and notice a change in the way Excel behaves, and I don't understand why. When I have a simple formula (in my budget, as an example), and move a line, my formula shows a #REF error (or errors). For example:
Bank $257.60 (on hand)

Electricity $209.00

Paypal $9.00 (bills)

Balance $39.60

The formula adds the cells between "electricity" and "Paypal" including the blank one in the middle and subtracts from the cell containing "257.60" to give the balance ($39.60). If I delete the row between (or cut and paste the "Paypal" line, I get the #REF error. On my last computer, it would automatically adjust to the new settings. Why would that be? And is there a simple fix for this? (The last laptop was also windows 7 and Excel 2007).

Thanks,
Patrick


See More: Moving cells in an Excel 2007 formula results in #REF issue.

Report •

✔ Best Answer
September 19, 2012 at 06:24:00
Guess what? it's your formula. :-)

Your problem is in how you have your formula constructed.

When you specify each cell in a range as in:

D6+D7+D8+D9

If you remove or delete one of the cells you will get a #REF error because the formula is trying to access an invalid cell.

Try this and see how it works:

=SUM(C4:C5)-SUM(D6:D8)

The SUM function will ignore any blanks or text.

MIKE

http://www.skeptic.com/



#1
September 18, 2012 at 07:18:57
I think we could be of more help if you provided the actual formula that you are using instead of just describing what it does.

Cell references for your example data would be helpful also.

The following link contains instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 18, 2012 at 17:01:15
Actually, Derbydad3, the formula is irrelevant. I wasn't asking for help with a formula. I was asking for help with a setting change that would allow me to move something within the cells contained in a formula (any formula) without causing an error, as it was with an identical setup before I switched to a new computer. I don't need the condescention and I'll find the answer elsewhere.

Report •

#3
September 18, 2012 at 17:19:33
Have you checked your options?

Click the Microsoft Office button
Click on the Excel/Word Options button to bring up the Excel Options dialog box (at the bottom of window)
Click on the Formulas button in the left hand pane
Under "Error Checking Rules"
Make sure you have Un-Checked the box "Formula Referring to Empty Cells"

See if that helps.

Also, using your data I am unable to duplicate your problem.

Perhaps it is your formula that is causing the problem.

A #REF error occurs when a spreadsheet formula contains incorrect cell references.


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 19, 2012 at 03:58:30
I'm not sure why you found my response condensending. It certainly wasn't meant to be.

By posting the formula, data and cell references, you would allow us to test your exact set up and perhaps offer a solution.

Since, as Mike said, reproducing the error wasn't possible with just your data, having the rest of the details might help us help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
September 19, 2012 at 04:04:52
Hi Mike. Thanks for the reply. Here is the actual formula used: =SUM((C4+C5)-(D6+D7+D8+D9+D10)) where C4+C5 are our incomes and the rest are the outgoes. Try the formula with this:
C4=500
C5=500
D6=100
D7=blank
D8=blank
D9=250
D10=60
Row 11 is blank.
The formula is in cell E12. If I delete the entire row 7 (and tell it to move cells up), instead of adapting the formula and automatically just deleting D7 out of it (like it used to do on the previous install), it gives me the #REF in the formula. I used to get =SUM((C4+C5)-(D6+D7+D8+D9)) (as it would even adapt that when it moved the rows up, it renamed them within the formula and left out the last row before the total). NOW I get =SUM((C4+C5)-(D6+#REF!+D7+D8+D9)). ALSO, if I go back to the original and drag D9 (250) to D8, I get this: =SUM((C4+C5)-(D6+D7+#REF!+D8+D10)). In order to preserve the formula, I have to copy and paste and then go back to the original cell and "clear contents". Cutting or deleting will result in the #REF as well.

And Derbydad03, perhaps it wasn't as condescending as it appeared after a generally long day, looking at it fresh with a new set of eyes this morning. I apologize for that comment. It was wrong. I know you were just trying to help.

Thanks,
Patrick


Report •

#6
September 19, 2012 at 06:24:00
✔ Best Answer
Guess what? it's your formula. :-)

Your problem is in how you have your formula constructed.

When you specify each cell in a range as in:

D6+D7+D8+D9

If you remove or delete one of the cells you will get a #REF error because the formula is trying to access an invalid cell.

Try this and see how it works:

=SUM(C4:C5)-SUM(D6:D8)

The SUM function will ignore any blanks or text.

MIKE

http://www.skeptic.com/


Report •

#7
September 19, 2012 at 08:16:47
Just by way of a more complete explanation of why your formula is incorrect:

Excel calculates from the "inside out",

=SUM((C4+C5)-(D6+D7+D8+D9))

So with your formula, the first thing it tries to calculate are the two expressions in parentheses ( ):

(C4+C5)
and
(D6+D7+D8+D9)

Your adding specific cells, if you remove one of the cells, one of the formula parameters is removed, and up pops the #REF error.

The SUM() function does not come into play until after the inner expressions have been evaluated.

MIKE

http://www.skeptic.com/


Report •

#8
September 19, 2012 at 15:51:46
Yannow, now that I look at it, ALL of my formulas before were "between this and that" with the colon. Didn't even occur to me! NOW I get it. I didn't even realize I was doing it differently when I set this formula up. Thanks!

Report •

Ask Question