Using the displayed text (not the value) of a cell

[Bruce McCausland]

The 'value' of cell A7 is =Staff!A2, but the 'displayed' text at A7 is a persons name (say, Smith_John). At C7 I need the formula =Smith_John!J21and at E7 I need the formula =Smith_John!K21. That is, I need to use whatever name is displayed in C7 as a worksheet name so I can go pull data from that employee's worksheet.

I'm guessing that there is a worksheet function that will extract the displayed data in C7 so it can be used in a formula, but I couldn't figure out from the help screens how to do it with the text worksheet function. Is there some other function that will do it?

 

[VoG]

In C7 try

=INDIRECT(A7&"!J21")

 

[Bruce McCausland]

Just perfect!! Thank you very much!!