Formula Link Help

[Aaron]

In the following formula I would like the [33320I.xls] portion to be linked to a cell in my spreadsheet, so that when the cell changes, the formula changes. Does anyone have a suggestion?? thx

=VLOOKUP($B5,'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)-VLOOKUP($B5,'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)

 

[just_jon]

In the following formula I would like the [33320I.xls] portion to be linked to a cell in my spreadsheet, so that when the cell changes, the formula changes. Does anyone have a suggestion?? thx

=VLOOKUP($B5,'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)-VLOOKUP($B5,'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)

Store the workbook name in a cell, say A1. Then reference the cell via INDIRECT as in --

=VLOOKUP(INDIRECT("$B5,'D:\Extracts\P03\["&$A$1&".xls]Sheet1'!$A$3:$U$2000"),C$3,FALSE)-VLOOKUP(INDIRECT("$B5,'D:\Extracts\P04\["&$A$1&".xls]Sheet1'!$A$3:$U$2000",C$3,FALSE)[/

 

[Aaron]

Thanks for the formula Jon. However, I am getting a #REF error, and my cell reference is correct any suggestions? Here is my new formula below.

=VLOOKUP(INDIRECT("$B5,'D:\Extracts\P03\["&$A$1&".xls]Sheet1'!$A$3:$U$2000"),C$3,FALSE)-VLOOKUP(INDIRECT("$B5,'D:\Extracts\P04\["&$A$1&".xls]Sheet1'!$A$3:$U$2000"),C$3,FALSE)

 

[Aladin Akyurek]

In the following formula I would like the [33320I.xls] portion to be linked to a cell in my spreadsheet, so that when the cell changes, the formula changes. Does anyone have a suggestion?? thx

=VLOOKUP($B5,'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)-VLOOKUP($B5,'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$3:$U$2000,C$3,FALSE)

=VLOOKUP($B5,INDIRECT("'D:\Extracts\P03\"&A5&"Sheet1'!$A$3:$U$2000"),C$3,0)-VLOOKUP($B5,INDIRECT("'D:\Extracts\P04\"&A5&"Sheet1'!$A$3:$U$2000"),C$3,0)

where A5 houses the string [33320I.xls].


You might consider using morefunc's INDIRECT.EXT instead of INDIRECT, so that you can work with closed workbooks.

 

[Aaron]

I get the same answer Aladin (#REF). All I did was copy and pasted your formula, and cell reference. What are you referring to with the other INDIRECT function?

 

[Aaron]

Here is my formula now:
=VLOOKUP($B5,INDIRECT("&$A$1&"),F$3,FALSE)-VLOOKUP($B5,INDIRECT("&$A$2&"),F$3,FALSE)

This is the content of A1: 'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$1:$U$2000

This is the content of A2: 'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$1:$U$2000


And I am still getting a #REF error. Can anyone offer another suggestion?? Thanks

 

[Aladin Akyurek]

Here is my formula now:
=VLOOKUP($B5,INDIRECT("&$A$1&"),F$3,FALSE)-VLOOKUP($B5,INDIRECT("&$A$2&"),F$3,FALSE)

This is the content of A1: 'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$1:$U$2000

This is the content of A2: 'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$1:$U$2000


And I am still getting a #REF error. Can anyone offer another suggestion?? Thanks

I don't understand why you change the question... I'll take up the original here.

In A1 enter: 33320I.xls (thus not: [33320I.xls])

and try...

=VLOOKUP($B5,INDIRECT("'D:\Extracts\P03\["&A1&"]Sheet1'!$A$3:$U$2000"),C$3,0)-VLOOKUP($B5,INDIRECT("'D:\Extracts\P04\["&A1&"]Sheet1'!$A$3:$U$2000"),C$3,0)

This formula requires that the target book is open. If you substitute INDIRECT.EXT for INDIRECT, the target book needs not to be open.

 

[just_jon]

Here is my formula now:
=VLOOKUP($B5,INDIRECT("&$A$1&"),F$3,FALSE)-VLOOKUP($B5,INDIRECT("&$A$2&"),F$3,FALSE)

This is the content of A1: 'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$1:$U$2000

This is the content of A2: 'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$1:$U$2000


And I am still getting a #REF error. Can anyone offer another suggestion?? Thanks

What's F$3?

 

[Aladin Akyurek]

Here is my formula now:
=VLOOKUP($B5,INDIRECT("&$A$1&"),F$3,FALSE)-VLOOKUP($B5,INDIRECT("&$A$2&"),F$3,FALSE)

This is the content of A1: 'D:\Extracts\P03\[33320I.xls]Sheet1'!$A$1:$U$2000

This is the content of A2: 'D:\Extracts\P04\[33320I.xls]Sheet1'!$A$1:$U$2000


And I am still getting a #REF error. Can anyone offer another suggestion?? Thanks

What's F$3?

Column specifier for the lookup table. The original post mentioned C$3 (for that matters).

 

[Aaron]

Aladin is right about F$3 (it is a column identifier. However he does seem to be upset with the way I change my questions?? Anyway, is INDIRECT.EXT part of an Addin on Excel97?? Unfortunately I will have to use this formula, because the workbooks have the same name, and therefore, I cannot have them open at the same time.