Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect functiion
Can anyone help me substitute an indirect function for the Workbook and tab
name in the following formula. I want my formula to use cell A1 to determine the workbook and tab to pull data from. =INDEX([GOODYEAR.xls]GT_0!$L$1:$N$98, MATCH($A6,[GOODYEAR.xls]GT_0!$L$1:$L$98,), MATCH("BUY",[GOODYEAR.xls]GT_0!$L$1:$N$1,)) Any help would be appreciated -- Regards, timmulla |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect functiion
Use 2 cells, say A1:B1, to house the book name and tab name, eg:
In A1: GOODYEAR In B1: GT_0 Then with GOODYEAR.xls simultaneously open, use: =INDEX(INDIRECT("'["&A1&".xls]"&B1&"'!L1:N98"),MATCH($A6,INDIRECT("'["&A1&".xls]"&B1&"'!L1:L98"),),MATCH("BUY",INDIRECT("'["&A1&".xls]"&B1&"'!L1:N1"),)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "timmulla" wrote: Can anyone help me substitute an indirect function for the Workbook and tab name in the following formula. I want my formula to use cell A1 to determine the workbook and tab to pull data from. =INDEX([GOODYEAR.xls]GT_0!$L$1:$N$98, MATCH($A6,[GOODYEAR.xls]GT_0!$L$1:$L$98,), MATCH("BUY",[GOODYEAR.xls]GT_0!$L$1:$N$1,)) Any help would be appreciated -- Regards, timmulla |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect functiion
Max,
Is this possible to do without having the referenced workbook open? I know the formula will have to be changed to include the file location, but I'm just wondering if it is even possible. I have been trying for awhile and it hasn't been working. Thanks, DoubleZ "Max" wrote: Use 2 cells, say A1:B1, to house the book name and tab name, eg: In A1: GOODYEAR In B1: GT_0 Then with GOODYEAR.xls simultaneously open, use: =INDEX(INDIRECT("'["&A1&".xls]"&B1&"'!L1:N98"),MATCH($A6,INDIRECT("'["&A1&".xls]"&B1&"'!L1:L98"),),MATCH("BUY",INDIRECT("'["&A1&".xls]"&B1&"'!L1:N1"),)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "timmulla" wrote: Can anyone help me substitute an indirect function for the Workbook and tab name in the following formula. I want my formula to use cell A1 to determine the workbook and tab to pull data from. =INDEX([GOODYEAR.xls]GT_0!$L$1:$N$98, MATCH($A6,[GOODYEAR.xls]GT_0!$L$1:$L$98,), MATCH("BUY",[GOODYEAR.xls]GT_0!$L$1:$N$1,)) Any help would be appreciated -- Regards, timmulla |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect functiion
Is this possible to do without having the referenced workbook open?
No, I'm afraid not. That's the requirement. Here's a relevant snippet from a Dave Peterson posting: " ... If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you... " You might want to check it out. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "DoubleZ" wrote: Max, Is this possible to do without having the referenced workbook open? I know the formula will have to be changed to include the file location, but I'm just wondering if it is even possible. I have been trying for awhile and it hasn't been working. Thanks, DoubleZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect ref in array formulas | Excel Worksheet Functions | |||
Copying a formulae down a column that includes an INDIRECT | Excel Discussion (Misc queries) | |||
Using INDIRECT in INDEX(LINEST.. ) function | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions |