Fixing sheet names is a 3D reference

  • Thread starter Thread starter Davidt
  • Start date Start date
D

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
 
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
 
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
 
Hi David

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