find a worksheet and extract data

D

Deborah

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.

e.g. In cell A1 I insert the date of the worksheet I want to use, e.g
01-01-2004.
I want to find a formula that looks for the worksheet dated 01-01-2004 and
gives me the data that is in cell B7 of that worksheet.

Can you help?

Thanks in advance.

Deborah
 
D

Deborah

Hi Roger,

Many thanks.
I thought I could resolve my problem but apparently it is complicated...
too complicated for me...

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))}

But it only looks for the data on worksheet 01-01-2004. Now I would like to
add a 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 me?

Many thanks

Deborah
 
R

Roger Govier

Hi Deborah

I think I would be inclined to try and simplify things.
On each of the 3 workbooks, I would insert a new column A, and in each
case in A2 enter
=B2&C2&D2
and copy down through A3:A8.
Then I would create named ranges, 2 in each sheet. e.g.
RangeA2004 = '01-01-2004'!$A$2:$A$8
RangeE2004 = '01-01-2004'!$E$2:$E$8

Repeat for 2005 and 2006
Then I would use
=INDEX(INDIRECT("RangeE"&$A$1),MATCH(B4&C4&D4,INDIRECT("RangeA"&$A$1)))
 
Top