Unclear how many rows each table has.
Suppose a table is just one row.
So here are the first three tables
1 apple 3 alpha
4 pear 6 beta
8 orange 11 gamma
On Sheet1 in A1 I enter a number, say 2
In Sheet1 in A2 I have formula =INDEX(Sheet2!A1:A36,Sheet1!A1)
This is copied across to D2
So Sheet1 displays
2
4 pear 6 beta


If you tables are more than one row we can make adjusts. Please tell us what
you need.
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fetzer" wrote in message
...

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