Refering to a Dynamic Worksheet Name in a Cell

[deepgrewal22]

I have a question pertaining to Microsoft Excel 2003. I have a workbook with many sheets. The sheets can be renamed by the end user. However, there is a Master sheet which refers to summary data from each sheet.

Currently, I can reference a particular cell from, let's say, Sheet1 as:

=Sheet1!A1

However, I would like to reference this sheet no matter what the end user decided to rename it. I know in the VBA module each Sheet has a property called name: Sheet1.Name. Without having to use the VBA module, is there any way that I can refer to a sheets name within a cell?

I know that the following code below doesn't work. Is there something similar that does?

=Sheet1.Name!A1

Deep Grewal
"Microsoft Works" - oxymoron

 

[SkipVought]

Hi,

You would be much better off to CONSOLIDATE your data into ONE TABLE, from which you could report in multiple tabs.

Summarizing from one table, is orderS of magnitude simpler and easier than trying to gather a summary form disparate locations, especailly when your users can change things like sheet names willy nilly.

However, if someone does change the sheet name, any references ALSO change. Do you have Automatic Calculation on? Or Calculate before saving?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[deepgrewal22]

Fortunatley, my end users are IT Systems Analysts. Unfortunately, each Tab represents a (1 - one) session of testing which the Analyst performed that day with an end user and this workbook design was set forth by the CIO's office. Therefore, I can do very little to change the physical design of the workbook. However, it would benefit me greatly if I could refer to a workbook's dynamic name. Any suggestions?

Deep Grewal
"Microsoft Works" - oxymoron

 

[AnotherHiggins]

You said you don't want to use a "VBA module".

Are User Defined Functions are out?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

VBA!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Deep Grewal,

as Skip said:

However, if someone does change the sheet name, any references ALSO change.

Can you say why this is not enough for you?



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[deepgrewal22]

Skip was correct in his statement:

However, if someone does change the sheet name, any references ALSO change. Do you have Automatic Calculation on? Or Calculate before saving?

I realized this today as I was testing the workbook. I changed the name of a spreadsheet (tab) and the formulas on the Master sheet also reflected the changed name. From Skip's recommendation, I was under the assumption that Automatic Calculation or Calculate Before Saving had to be enabled for this to function correctly.

I thank you all for your time and input into this thread. I have met the objectives put forth by my superiors in making this workbook function in accord with their specifications.

Deep Grewal
"Microsoft Works" - oxymoron