Changing worksheet name within a formula (automatically)
 

Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

Changing worksheet name within a formula (automatically)
 

It looks like you are missing a single quote at the beginning of the
worksheet name

=INDIRECT("'Promotional Detail "&A1&" "&A2&"'!G34")


"dave" wrote:

Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

Changing worksheet name within a formula (automatically)
 

Your formula worked fine for me.

Are you sure you have the names of the sheets correct (maybe a trailing
space--or multiple internal spaces)???

If you're sure you have it right, what does not working mean?

dave wrote:

Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

--

Dave Peterson

Changing worksheet name within a formula (automatically)
 

Try
=INDIRECT("'Promotional Detail"&" "&A1&" "&B1&"'!G34")
Before the P we have " then '
Before the G we have " then ' followed by !
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"dave" wrote in message
...
Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to
change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover
sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and
a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

Changing worksheet name within a formula (automatically)
 

It works brilliant thank you very much for your help extremely useful.

Thanks again

Dave


"Dave Peterson" wrote:

Your formula worked fine for me.

Are you sure you have the names of the sheets correct (maybe a trailing
space--or multiple internal spaces)???

If you're sure you have it right, what does not working mean?

dave wrote:

Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

--

Dave Peterson

Changing worksheet name within a formula (automatically)
 

I think you meant this note for Joel and Bernard. They both caught the missing
leading apostrophe.

But I bet that they're both glad you got it working.

dave wrote:

It works brilliant thank you very much for your help extremely useful.

Thanks again

Dave

"Dave Peterson" wrote:

Your formula worked fine for me.

Are you sure you have the names of the sheets correct (maybe a trailing
space--or multiple internal spaces)???

If you're sure you have it right, what does not working mean?

dave wrote:

Hello please help,

I have a summary worksheet with a formula taking data from another
worksheet. Simply ='worksheet'!G34. At certain times of year I have to change
the worksheet name to equal the current period in all of the cells in the
summary sheet. I have worksheets called "promotional detail LE01 2007" all
the way through to LE12.
I have in the summary sheet as a header LE01 which changes by a cover sheet.

Is there a way I can have the formula so it changes the worksheet name to
correspond with the header.

E.G cell A1 = LE01 cell A2 = 2007
formula ='Promotional Detail LE09 2007'!G34

Is there a way so that the end of the worksheet name can equal cell a1 and a1.
I have tried the following but it is not working please help.

=INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34")

Many thanks in advance

--

Dave Peterson


--

Dave Peterson