Fixing sheet names is a 3D reference
 

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

Fixing sheet names is a 3D reference
 

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

On Thu, 24 Jan 2008 16:16:00 -0800, Davidt
wrote:

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

Fixing sheet names is a 3D reference
 

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

"Gord Dibben" wrote:

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

On Thu, 24 Jan 2008 16:16:00 -0800, Davidt
wrote:

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

Fixing sheet names is a 3D reference
 

FYI Gord, same for 2007

--

Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 24 Jan 2008 16:16:00 -0800, Davidt

wrote:

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

Fixing sheet names is a 3D reference
 

Hi David

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

--

Regards
Roger Govier

"Davidt" wrote in message
...
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

"Gord Dibben" wrote:

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

On Thu, 24 Jan 2008 16:16:00 -0800, Davidt

wrote:

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