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