Lookup Fn using a dynamic filename

Mar 13th, 2002, 08:02 AM

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 · Mar 13th, 2002, 09:48 AM

Quote:

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 · Mar 15th, 2002, 08:22 PM

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 · Mar 15th, 2002, 09:29 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 08:02 AM	#1
Guest Posts: n/a	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

Mar 15th, 2002, 08:22 PM	#3
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	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

Mar 15th, 2002, 09:29 PM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 ]