3D Cell referencing help

[oli3659]

Hi everyone

Trying to do an assignment and this is getting on my nerves; 3D cell referencing

Instead of having loads of screen shots I have just uploaded the spreadsheet.

In Sheet 1 I am totalling the sales for the Bracknell + Wokingham sheets. Need B5:H13 to all be totalled for both locations. All of them are done but Europe seems to be not working..

In the D column its =SUM(Bracknell:Wokingham!D5) and on the C column its =SUM(Bracknell:Wokingham!C5)

There is no data in the Wokingham branch sheet so the numbers should be the same as the Bracknell sheet. But £353,000 doesn't add up.

Am I doing something wrong or am I just blind :D?
Thanks

The cells with problems I have highlighted in red

[Colin Legg]

Hi Oli,

This formula:

Please Login or Register  to view this content.

will sum cell D5 on every sheet between Bracknell and Wokingham inclusive.

So:
Bracknell!D5 = £53,000
Targets!D5 = £300,000
Wokingham!D5 = £0

Total = £353,000


If the targets sheet should not be included in the sum then move it to the right of the Wokingham sheet.

[oli3659]

I would have never thought of that.

Thanks for the quick response :D

[oli3659]

Weird, I saved the spreadsheet and loaded it back up. It's loading in compatibility mode. But I also get this error

\1
Not exactly sure what that means or where it has been used.

[Colin Legg]

In the Wokingham sheet cells C11 to H11 and H26 you have formulas which reference the cells the formulas are contained within.

For example, cell C11 has this formula:

Please Login or Register  to view this content.

This means it's trying to SUM itself, which could lead to an infinite calculation.

Change the formula so it is like this:

Please Login or Register  to view this content.

The same applies to D11, E11, F11, G11, H11 and H26.

[oli3659]

Oh right, I know why it's done that.

All sorts of weird things are going on such as in Bracknell and Wokingham sheet.

Cell C15-G15 weren't changing if I changed the numbers in the cells above. If I clicked on C15:G15 and pressed enter it would turn to £0.
A few open and closes of excel fixed that, for now at least :S

Thanks again

[Colin Legg]

Hi,

Cell C15-G15 weren't changing if I changed the numbers in the cells above. If I clicked on C15:G15 and pressed enter it would turn to £0.
A few open and closes of excel fixed that, for now at least :S

That's because of the circular references. Once you've fixed them you should be good to go.

[oli3659]

Off and running

Now just got to find a use for drop down list, hiding cells, a lookup table. Find out what error trapping is. And make a trendline using the Africa figures =/

EDIT: So I make a new sheet, and randomly data validation from the Bracknell sheet comes up I tried to type in March and it gave me the validation rule of ha to be between 1+1000000. I used that validation on a different sheet and it wasn't even in the cell I was typing in. :S