A relatively easy way to do this would be to give each of your 36 tables a
name. The first part of all table names would be "common", such as MyTable
with a number from 1 to 36 to indicate a specific table. So you would have
MyTable1, MyTable2, MyTable3 ... MyTable34, MyTable35, MyTable36

Then in your VLookup formulas that need to reference a table based on the
entry in A1, your formula would look something like:
=VLOOKUP(A3,INDIRECT("MyTable" & A1),2,FALSE)
The important part here is the INDIRECT() portion, which will take the
contents of A1 and concatenate it to the 'common' table name "MyTable" to
pick up the correct table.

To give a table a name, select all of the cells in the table, then type its
name into the 'Name Box' and press the [Enter] key. The Name Box is the box
in the formula bar that usually shows you the address of the cell currently
selected/active. Look up Excel's Help for 'Define named cell references or
ranges' for more help on this subject.

"fetzer" wrote:


Not sure what function to use; vlookup, ISNA, MAtch, etc.. One of my
spreadsheets (sheet 2) has 36 different tables (not a pivot table) of 4
columns each where data is entered. The main spreadsheet (sheet 1) has
a cell A:1, where I would like to enter the number 1 through 36. In
other words, if I wish to extract the data from table 5, I enter 5 in
cell A:1 which then looks for that particular table and extracts all
the data and enters it into the main spreadsheet. Any help would be
appreciated.




--
fetzer
.