Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a worksheet and extract data
I have 3 different worksheets, "01-01-2004", "01-11-2005", "01-01-2006".
In a separate spreadsheet I want to insert a formula that gives me data from the worksheet I specify. At the moment I use the following formula: {=INDEX('01-01-2004'!$D$2:$D$8,MATCH(B4&C4&D4,'01-01-2004'!$A$2:$A$8&'01-01-2004'!$B$2:$B$8&'01-01-2004'!$C$2:$C$8,0))} Which works fine but it only looks for the data on worksheet 01-01-2004. Now I would like to add a fromula to this formula so I can get the data of the other worksheets if A1 is equal to the name (=date) of the worksheet. Can you help? Thanks in advance. Deborah |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a worksheet and extract data
With A1 pre-formatted* as text, and housing the sheetname, eg: 01-11-2005
*or, with the sheetname input in A1 made with a preceding apostrophe (the sheetname must be entered as text in A1) Try this in say, E4, array-entered (press CTRL+SHIFT+ENTER): =IF(A1="","",INDEX(INDIRECT("'"&A1&"'!D2:D8"),MATC H(B4&C4&D4,INDIRECT("'"&A1&"'!A2:A8")&INDIRECT("'" &A1&"'!B2:B8")&INDIRECT("'"&A1&"'!C2:C8"),0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Deborah" wrote: I have 3 different worksheets, "01-01-2004", "01-11-2005", "01-01-2006". In a separate spreadsheet I want to insert a formula that gives me data from the worksheet I specify. At the moment I use the following formula: {=INDEX('01-01-2004'!$D$2:$D$8,MATCH(B4&C4&D4,'01-01-2004'!$A$2:$A$8&'01-01-2004'!$B$2:$B$8&'01-01-2004'!$C$2:$C$8,0))} Which works fine but it only looks for the data on worksheet 01-01-2004. Now I would like to add a formula to this formula so I can get the data of the other worksheets if A1 is equal to the name (=date) of the worksheet. Can you help? Thanks in advance. Deborah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find a worksheet and extract data | Excel Worksheet Functions | |||
Extract data from one worksheet | Excel Worksheet Functions | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
Extract data from one Worksheet to another | Excel Worksheet Functions | |||
extract data from worksheet | Excel Worksheet Functions |