Changing worksheet name within a formula (automatically)

[dave]

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

 

[Joel]

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

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

 

[Dave Peterson]

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?

 

[Bernard Liengme]

Try
=INDIRECT("'Promotional Detail"&" "&A1&" "&B1&"'!G34")
Before the P we have " then '
Before the G we have " then ' followed by !
best wishes

 

[dave]

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

Thanks again

Dave

 

[Dave Peterson]

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.