Dynamic worksheet references

[David Boothe]

I want to do something like this...

1st worksheet name "targetTab"
2nd worksheet name "sourceTabA"
3rd worksheet name "sourceTabB"



In cell A1 of the sourceTabA worksheet is "APPLES"
In cell A1 of the sourceTabB worksheet is "BANANAS"

I want a formula to put in cell A1 of the targetTab worksheet so that
when I enter "sourceTabA" in cell A2 of the worksheet the formula
returns "APPLES" then when I enter "sourceTabB" in cell A2 the formula
returns "BANANAS".

Can someone help me with this please?


Thanks!

 

[Ardus Petus]

=INDIRECT(A2&"!A1")

HTH

 

[aresen]

=INDIRECT(A2&"!A1")

 

[Dave Peterson]

Sometimes references to worksheets require that the worksheet name be surrounded
by apostrophes:

=INDIRECT("'" & A2 &"'!A1")

If they're not needed, they won't hurt.