EXCEL - FORMULA TO CREATE A TAB REFERENCE

[Dan Wiese]

I WANT TO LOOKUP INFORMATION IN MULTIPLE SHEETS AND WOULD LIKE TO BE ABLE TO
USE A FORMULA TO REFERENCE A CELL IN THE CURRENT SHEET WITH THE SPECIFIC
SHEET NAME THAT I WANT TO DO THE LOOKUP TO. MORE SPECIFICALLY, EACH OF MY
SHEETS ARE A STORE NUMBER, 201, 202, 203, ETC., AND IN THE SUMMARY SHEET I
HAVE THE STORE NUMBERS LISTED IN A COLUMN. IS THERE A FORMULA TO USE THE
STORE # FROM THE CURRENT SHEET'S CELL REFERENCE THAT WOULD USE IT IN THE
FORMULA AS THE TAB NAME? I HOPE I EXPLAINED THIS GOOD ENOUGH. IT WOULD SAVE
ME A LOT OF TIME. ANY HELP IS APPRECIATED. EXAMPLE: IF CELL A1 CONTAINED
THE NUMERICAL VALUE OF 201 AND THE TAB I WANT TO LOOKUP TO WAS SHEET 201,
WHAT FORMULA WOULD LOOK AT CELL A1 AND PUT THE SHEET NAME OF 201 IN THE
LOOKUP FORMULA?

 

[Pete_UK]

Dan,

in future do not submit posts that are all in capitals (or SHOUTING) -
it makes them difficult to read, and is considered rude.

It also helps if you could describe how your data is laid out in your
sheet(s), i.e. what columns you use for what data. You may be able to
use VLOOKUP or an INDEX/MATCH combination, but you will probably have
to use INDIRECT within these functions to define the lookup table,
something like:

INDIRECT("'"&A1&"'!A1:F100")

assuming your tables occupy A1:F100 on each sheet.

Hope this helps.

Pete