find a worksheet and extract data
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,INDI RECT("RangeA"&$A$1)))
--
Regards
Roger Govier
"Deborah" wrote in message
...
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
"Roger Govier" wrote:
Hi Deborah
Try
=INDIRECT("'"&$A$1&"'!B7")
Do note the quotes " ' " " ' ! B7"
--
Regards
Roger Govier
"Deborah" wrote in message
...
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
|