#DIV/O with simple =sum() formula

[santidreamer]

I have a #DIV/O error with a simple summary sheet formula:

=SUM('Sage PV 6:Saffron PV 7'!D4)

This formula works fine in adjacent cells.

All (19) D4 cells in the range and cells related to them have:

Been formatted as numbers and are the result of other numbers

Are positive values

Are not zero

None are divided by anything.

If fact 0 and / form no part of this formula or D4

 

[GlennUK]

When you trace precedents on the cell, what references do you get? Do you get any cells that you haven't checked?

 

[Jonmo1]

The only reasonable explaination is that one of the D4 cells between
Sage PV 6 and Saffron PV 7
contain a Div/0 error.

Check HIDDEN sheets
A hidden sheet will be included if it was between the 2 named sheets prior to being hidden.

 

[santidreamer]

Thank you both so much.

It was a (forgotten) hidden sheet which became a land mine when I copy an adjacent sheet.


I love the trace precedents function, but it fails (me) when the precedent cells are not on the current worksheet.

Unless you know a way of tracing across multiple work sheets?

 

[GlennUK]

Instead of using the trace precedents shortcut key combination use the trace precedents button ... you will get an arrow connected to a sheet icon, and if you double-click that you'll get the Goto Dialog to allow you to navigate to the precedent references.

 

[santidreamer]

The only way I know is Formula-Trace precedents.

When the precedent is on a different work sheet a small box and cross hatch icon appears.

Clicking on this box with a blue line across the top yields no further information.

 

[GlennUK]

You say "Clicking on this box ...." ... did you actually double-click, as I said?

 

[santidreamer]

Yes.

2x left makes the icon disappear and highlight the cell.

2x right show normal right click options, cut, copy etc.

I am using Excel 2010