Using a cell to change the filename reference

[rkeithg]

I would like to be able to use cell (B1) to change the file name referenced in a lookup formula.

Example: A1 contains a company number "503"

A2 is a lookup from March's sales file =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

I would like to replace the [March.xls] with a cell reference within my file so that I can change my lookup by changing "March.xls" to "April.xls".

_________________
Thanks
rkeithg

[ This Message was edited by: rkeithg on 2002-04-10 12:13 ]

[Asala42]

Hiya

I think you want something like the INDIRECT function. Instead of:

=VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

Try:


=VLOOKUP($A1INDIRECT(Z1),2,FALSE)

where Z1 contains the text string: 'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184

I believe the file needs to be open for this to work however, kind of a big drawback.

Hope that helps somewhat
Adam

[Aladin Akyurek]

On 2002-04-10 12:12, rkeithg wrote:
I would like to be able to use cell (B1) to change the file name referenced in a lookup formula.

Example: A1 contains a company number "503"

A2 is a lookup from March's sales file =VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

I would like to replace the [March.xls] with a cell reference within my file so that I can change my lookup by changing "March.xls" to "April.xls".

_________________
Thanks
rkeithg

[ This Message was edited by: rkeithg on 2002-04-10 12:13 ]

Your formula appears to show typos -- It's probably:

=VLOOKUP($A1,'C:ACCTSALES[March.xls]Sheet1'!$A$4:$M$184,2,FALSE)

Assuming that B1 houses [April.xls], try:

=VLOOKUP($A1,INDIRECT("'C:ACCTSALES"&B1&"Sheet1'!$A$4:$M$184"),2,FALSE)

Aladin