Vlookup Reference Certain Worksheets

[Lindsey]

Every month I receive a new worksheet with that month's data. I have a master
sheet that I use to pull the data from this new worksheet. However, I have to
go through all of the vlookup formula and change the referenced worksheet
name. Is there a way to change the formula, so that it references a cell (A1)
with the worksheet name?

Example:
=VLOOKUP(J$3,Q109!$U$6:$GM$20,$C12,FALSE)
This is the formula everything is hooked up to, i.e. Q109 worksheet

I then have to go through each formula and change it to Q209 worksheet.
=VLOOKUP(J$3,Q209!$U$6:$GM$20,$C12,FALSE)

Can I just have it reference a particular cell and when I change the name in
the cell, all of the formula change which worksheet they reference?

Thanks for you help!

 

[Jim Thomlinson]

You can use the Indirect function something like this...

=VLOOKUP(J$3,Indirect(A1 & "!$U$6:$GM$20"),$C12,FALSE)
Where Q209 is in cell A1

Note that Indirect is a volatile function which means that it will
recalcualte every time a calculation runs in XL. If you have a lot of these
formulas you could notice a substantial performance loss.

 

[KC]

Hi Lindsey,
In Edit menu, choose Replace (Ctrl+H), if you expand "Options >>" button you
can see in "Look in:" field that there is 'Formulas', which means this will
replace the reference of the cells as well.

so try replacing Q109 with Q209.
do this using "Find Next" followed by "Replace" to be sure that it works the
way you want it, and once your sure you can use "replace all".

hope this works?

-kc
* click YES if this works!

 

[Lindsey]

That was very helpful, thanks!!!!

You can use the Indirect function something like this...

=VLOOKUP(J$3,Indirect(A1 & "!$U$6:$GM$20"),$C12,FALSE)
Where Q209 is in cell A1

Note that Indirect is a volatile function which means that it will
recalcualte every time a calculation runs in XL. If you have a lot of these
formulas you could notice a substantial performance loss.

 

[Lindsey]

Thank you, that worked as well!

Hi Lindsey,
In Edit menu, choose Replace (Ctrl+H), if you expand "Options >>" button you
can see in "Look in:" field that there is 'Formulas', which means this will
replace the reference of the cells as well.

so try replacing Q109 with Q209.
do this using "Find Next" followed by "Replace" to be sure that it works the
way you want it, and once your sure you can use "replace all".

hope this works?

-kc
* click YES if this works!