#REF Error When Copying A Sheet In A Sum Range

[centerNegative]

Here's my issue, the basic version. Let's say I have a new blank workbook with the standard three base sheets; Sheet1, Sheet2, Sheet3. In cell A1 of each worksheet I'll input a number; for the purposes of ease I'll just input "1". In cell A2 of each worksheet I'll input the following formula: =SUM(Sheet1:Sheet3!A1), so cell A2 of all the worksheets displays "3". Now, if I copy Sheet1 (to any position), the formula in the newly copied sheet ('Sheet1 (2)') breaks and generates a #REF error ('=SUM(#REF!A1)'). However, if I copy either Sheet2 or Sheet3, no error is encountered in the copied sheets. I'd be grateful for any explanation of this behaviour and a method for dealing with it. As usual, I'd like to avoid any VBA solutions for the time being and hope for a workaround using base Excel formulas. I'd also like to not have to create a hidden sheet that acts as the first sheet in the range as a workaround. Thanks for reading!

[jeffreybrown]

A great way to sum across all sheets is too create two sheets, one called first and one called Last.

Now put everything in-between those two sheets and use...

=SUM(First:Last!A1)

[centerNegative]

A great way to sum across all sheets is too create two sheets, one called first and one called Last.

Now put everything in-between those two sheets and use...

=SUM(First:Last!A1)

@jeffreybrown

That was the exact thing I was hoping to avoid. For the sake of tidiness, I don't want to create superfluous sheets like this, even if they can be hidden and kept out of the way, because my formulas rely on strict, self-explanatory naming conventions in order to be user-friendly for both Excel novices and experts. I'm almost more interested in why this behaviour occurs in the first place as there are many obvious workarounds but I'd like to keep the solution direct.

@popipipo

As for the single quotes, Excel is accounting for those automatically with copied sheets without me manually adjusting the formulas. If Sheet2 and Sheet3 are copied, the A2 cell will then reflect "5" for totaling the five worksheets. The issue is simply when Sheet1 is copied the sum formula worksheet references are completely destroyed for reasons I don't understand.

Thanks for your responses.

[popipipo]

Your missing the qoutes

=SUM('Sheet1:Sheet3 (2)'!A1)

You needs those if there are spaces in the sheetnames.

[FDibbins]

@ popipipo...you only really need the ' if there is a space in the sheet name - although it does no harm to include them like that

Edit: It was brought to my attention that the sum() you used does in fact have spaces...
=SUM('Sheet1:Sheet3 (2)'!A1)
so you were correct to use ' aplogies

[jeffreybrown]

I'd be grateful for any explanation of this behaviour and a method for dealing with it.

Seems I gave you a method to deal with it in =SUM(First:Last!A1)

It seems the reason you get the =SUM(#REF!A1) is because when you copied Sheet1 and created Sheet1 (2) it is not part of the original =SUM(Sheet1:Sheet3!A1) so it seems Excel can't deal with it...

By creating the First sheet and the Last sheet and placing everything in-between, Excel seems to always know where to start, where to end, and what's in-between.

If this does not help then maybe popipipo or FDibbins has an alternate solution or explanation...

[FDibbins]

my formulas rely on strict, self-explanatory naming conventions in order to be user-friendly for both Excel novices and experts

I dont see how this will impact on having start - and end - (blank) helper sheets. They do not need to have data on them, and you could even give them a background that says "Not for Use" or something.

Using this method is almost SOP for both more experienced and novice excel users alike, and keeps things simple and clean.

I guess what I'm saying is - I dont have anything more to add than Jeff already has, sorry

[centerNegative]

Appreciate all the feedback!