reference a cell with text (sheet name) so the V lookups work correctly

[WillardNC]

I have a sheet template that I have created to incorporate into various workbooks. This template sheet has a number of Vlookup formulas. The problem is that, when I insert my new template sheet into the current file, the formulas reference the old sheet in the old file. I would like to have one cell in my document (kind of like an anchor cell) where I could type in the name of the sheet where the data is contained. Then, have my vlookups incorporate that cell and the text was written applied to the formula.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Example:
Cell O6 = Basic Unit Costs
<o></o>
Currently, the v lookup cell once imported into the workbook shows: =VLOOKUP(AA16,'Costs(Unit_Hours)'!$B:$BB,22,FALSE)
<o></o>
I want to have the v lookup formula use the text I write in cell O6 make the v lookup flow to the proper main sheet named cell as it is a REAL pain to change all the V lookups by hand..
<o></o>
V lookup =VLOOKUP(AA16,'__(Use O6 Text Here)__'!$B:$BB,22,FALSE)
I have tried using the Indirect formula but keep getting ref errors and name errors and pretty much all errors.
<o></o>
I want to be able to import my template sheet into my workbook, then type the name of the sheet that contains the sheet name into cell O6, then all the V lookups pull data from the correct sheet. Any thoughts?
<o></o>
I hope this makes sense.

 

[Firefly2012]

Hi

You can use INDIRECT for this:

=VLOOKUP(AA16,INDIRECT("'" & O6 & "'!$B:$BB"),22,FALSE)

So you would place the entire sheet name in cell O6

 

[WillardNC]

THANK YOU so much! I honestly did not think it would work becuase I have played around with the indirect function so much. I pasted the formula in and BAM! It flowed correctly. Thanks again!