Lookup Fn using a dynamic filename

[Guest]

I am trying to use lookup on a dynamic file list.
I have a number of files in a directory, I want to do a lookup in.

So,

lookup(1,'[File1]FormX'!$b3:$b1030,'[File1]FormX'!$c3:$c1030)
lookup(1,'[File2]FormX'!$b3:$b1030,'[File2]FormX'!$c3:$c1030)

Where file1, file2 etc come from a directory list.

I can get file1 and file2 etc easily. What I can’t seem to do is dynamically create the lookup command!!
I can create the text of the lookup function, I just cant tell it it’s a function rather than text!

Any help would be most appreciated!

BJ

 

[Aladin Akyurek]

On 2002-03-13 07:02, Anonymous wrote:
I am trying to use lookup on a dynamic file list.
I have a number of files in a directory, I want to do a lookup in.

So,

lookup(1,'[File1]FormX'!$b3:$b1030,'[File1]FormX'!$c3:$c1030)
lookup(1,'[File2]FormX'!$b3:$b1030,'[File2]FormX'!$c3:$c1030)

Where file1, file2 etc come from a directory list.

I can get file1 and file2 etc easily. What I can’t seem to do is dynamically create the lookup command!!
I can create the text of the lookup function, I just cant tell it it’s a function rather than text!

Any help would be most appreciated!

BJ

Longre's UDF called EVAL might help here.

For example:

=EVAL(""&E1&"(A1:A4)")

where E1 houses the text 'SUM'.

The formula causes SUM to sum the range A1:A4.

You can download morefunc that contains EVAL at:

http://perso/wanadoo.fr/longre/excel/downloads/

 

[Rocky E]

I am not clear on everything, but somethimes you can back in to things and make things work. INDEX() and INDIRECT() come to mind. If you can set the formula up to reference a cell with one of these than you can dynamically change that cell and the formula should continue to work.
HTH
Rocky

 

[Dave Patton]

You can use Indirect.
1. File name in B2
2. Lookup ranges are named rL

3. formula

=VLOOKUP(A3,INDIRECT(B2&".xls!rL"),2,0)

comments
- do not have to worry about sheet name
since the range is named rL in each file
- to use Indirect, the files must be open
This message was edited by Dave Patton on 2002-03-15 20:34