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

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