Using Tab Number as VLOOKUP?

[momtoaj]

I have no idea how to do this. I am building a weekly menu/shopping list to
ease my grocery shopping problems. I have created a list of menu's that
coordinate with an ID. IE...1-Cheeseburger Macaroni, 2-Broccoli Cheese Soup,
etc. Then I have a tab marked 1 & on that sheet, list the ingredients I need
to purchase to make Cheeseburger Macaroni. The next thing I want to do is
have the spreadsheet go to each of the 10 menu items (aka tabs) I select &
pull over all of the ingredients from each of those 10 individual tabs to
make 1 shopping list alphabetically. Does that make sense? My first thought
was to use VLOOKUP to reference the appropriate tab & pull over the data from
that tab but I can't figure out if it is even possible to put a cell
reference into a VLOOKUP but have the cell reference actually referencing the
tab that it needs to look at for the ingredient list. If there is a
different method that makes more sense or would be easier, I would be open to
any suggestions.

 

[Chip Pearson]

You can use the INDIRECT function. For example,

=VLOOKUP("value",INDIRECT(A1)&"A1:B10",2,FALSE)

This will look for the word "value" in cells A1:A10 on the sheet whose
name is in cell A1 of the same sheet that contains the formula.

Basically, INDIRECT takes any text string and converts it to an actual
sheet/cell reference that can then be used by other functions.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Luke M]

Take a look at INDIRECT in XL's help file, I believe it is the key to what
you are wanting to do.

 

[Dave Peterson]

Typo alert...

=VLOOKUP("value",INDIRECT(A1)&"A1:B10",2,FALSE)
should look more like:
=VLOOKUP("value","'"&INDIRECT(A1)&"'!A1:B10",2,FALSE)

Some worksheet names will require that they be surrounded by apostrophes. And
if they're not needed, it won't hurt the formula. And the syntax needs
anexclamation point between the sheet name and the address of the lookup range.