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. 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Search a worksheet, extract rows using a list from another sheet | Excel Discussion (Misc queries) | |||
Extract data from one Worksheet to another | Excel Worksheet Functions | |||
extract data from worksheet | Excel Worksheet Functions |