Referencing to tables

**The Guy** · 03-05-2009, 03:54 AM

ok, I have an interesting dilema. I'm not even sure what forum to post it in, but here goes. I want to use a drop down box on Sheet 1 to select from a list of tables on sheet 5. Information from the selected table will then be used in simple math formulas in various other cells on different pages to give my final output values. I've seen information about vlookup and hlookup and index/offset and match. The problem is that the info I need to reference isn't in the same column or row as the table title, which is what needs to go on the drop down list.

**DonkeyOte** · 03-05-2009, 04:09 AM

You will need to use INDIRECT -- see XL Help for more info or run a Search on the Board. Beware that INDIRECT is Volatile.

**JBeaucaire** · 03-05-2009, 04:13 AM

If you "name" your tables (the data portion) with the same names as the choices in the dropbox, you can use an INDIRECT formula to pinpoint the tables by replacing the "range" parameter with the INDIRECT reference to your dropbox cell.

If the cell is in A1, and the choices were

Table1
Table2
Table3
Table4

And your normal formula read something like:

=INDEX(Sheet5!B2:H20,MATCH(etc....

Then you could reference the cell A1 on sheet1 indirectly like so:

=INDEX(INDIRECT(Sheet1!A1),MATCH(etc....

If you had chosen Table2 from the dropbox, this would make the formula evaluate to:

=INDEX(Table2,MATCH(etc....

...and if that represented a named range of cells on Sheet5, you're in business.

Will this help?