Indirect functiion

T

timmulla

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
 
M

Max

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"),))
 
D

DoubleZ

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 said:
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"),))

timmulla said:
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
Click to expand...
Click to expand...
 
M

Max

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
 
Top