Sheet name in a formula

[binaryfingers]

Hi

I have the following formula

=VLOOKUP($B53,Pricingsheet2006value,MATCH(PS2!GN56,Pricingsheet2006date,0)+1,FALSE)*GN59

and in the next cell

=VLOOKUP($B53,Pricingsheet2006value,MATCH(PS2!GO56,Pricingsheet2006date,0)+1,FALSE)*GO59

In Cell A1 I have the sheet name, in this example PS2

Is there a way to have the formula use the data in Cell A1 instead of hard coded the sheet name into the formula.

I have tried to play around with the Indirect() but due to the cell changing each time (eg GN56 and in the next cell GO56) I was unable to achieve the desired result.

Thanks for the help and support..

 

[binaryfingers]

Okay, I have now managed to edit the formula to get the correct sheet name, however I am still struggling to make the fomula dynamic in that it can be used in every cell

heres is the current formula

VLOOKUP($B53,Pricingsheet2006value,MATCH(INDIRECT($A$1&"!GN56"),Pricingsheet2006date,0)+1,FALSE)*GN59

Now I just need to figure out how to make GN56 be dynamic rather than hard coded..

Look forward to any help
Thanks,

 

[binaryfingers]

Okay, I now have the correct formula, thanks to Tek-Tips search and archive, piecing bits of useful information together.

=VLOOKUP($B53,Pricingsheet2006value,MATCH(INDIRECT($A$1&"!"&ADDRESS(ROW()-2,COLUMN(),4)),Pricingsheet2006date,0)+1,FALSE)*GN59

If anybody can suggest a more effective way, Im sure all users will be grateful of the advice.