Fixing sheet names is a 3D reference

[Davidt]

I have a workbook containing several worksheets and a summary sheet that
refers to all the other sheets with a 3D reference. I need to fix the 3D
reference just as I can fix a cell address by entering $A$5 (eg).

So my reference would appear as =SUM($Sheet1:$Sheet10:B5) - this would allow
me to delete, rename the boundary sheets without the reference being affected
in the summary sheet.

Is there any way to do this or otherwise protect the reference in summary
sheet?

Thanks

 

[Gord Dibben]

No such feature in Excel versions <2007....can't speak for 2007.

Try this method...............

Add a new blank sheet to the right of your Summary sheet

Add a new blank sheet at end.

Name these Start and End.

On your Summary sheet in a cell enter =SUM(Start:End!B5)

This will sum all B5's on sheets between your two dummy sheets

In future when adding/deleting sheets do so between Start and End.


Gord Dibben MS Excel MVP

 

[Davidt]

That doesn't work for me as the first sheet behind the summary is directly
referenced but it's data has to be reset each week.

My own solution was to copy the "week0" sheet to before "week52", then
rename "week0 (2) to "weekx" and then delete the contents of "week0"

It works, but thanks anyway

 

[Roger Govier]

FYI Gord, same for 2007

 

[Roger Govier]

Hi David

Post the formula that you currently are using, to see why Gord's suggestion
did not work for you.