Home |
Search |
Today's Posts |
#1
|
|||
|
|||
What funtion to use?
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.
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What funtion to use?
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What funtion to use?
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
funtion SUMAR.SI | Excel Discussion (Misc queries) | |||
worksheet funtion help | Excel Discussion (Misc queries) | |||
If Funtion | Excel Worksheet Functions | |||
If funtion help | New Users to Excel | |||
IF funtion | Excel Discussion (Misc queries) |