Dynamic VLOOKUP

[zippernek]

Would like to enter the text "November" in a cell on a summary tab and have a vlookup go to the "November" tab and pull back the appropriate data. Next month I'd like to enter "December" and have it bring back December's data...and so on. But I don't want to have to edit they vlookup (there are many) each month.

Lookup works fine if I directly reference the tab: =VLOOKUP(a10,November!$A:$J,10,FALSE)

What I would like to do is replace "November!$A:$J" with cell reference so each month I won't have to remember to change the date in all the lookups. There are numerous rows utilizing the vlookup and I have numerous worksheets doing the same thing, so relying on me to remember to change all the vlookups each month is dicey at best.

Assuming the name of the month is in Cell A1 on the summary tab, then: =VLOOKUP(a10,A1&"!$A:$J",10,FALSE) would be ideal. However, best I can do is get a "#VALUE!" error.

Any thoughts?

[alansidman]

I think if you change the formula in your vlookup to include the Indirect function it should work.

Look here for the syntax

http://stackoverflow.com/questions/9...-function-work

Alan

[Ace_XL]

Try..
=VLOOKUP(A10,INDIRECT($A$1&"!$A:$J"),10,FALSE)

[zippernek]

Perfecto!

That solution worked like a champ.

Thanks alansidman and ace_xl for your speedy responses.