#REF Error When Copying A Sheet In A Sum Range

Originally Posted by centerNegative

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...