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.

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

find a worksheet and extract data
 

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

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