This is a discussion on Formula Link Help within the Excel Questions forums, part of the Question Forums category; In the following formula I would like the [33320I.xls] portion to be linked to a cell in my spreadsheet, so ...

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)

2. ## Re: Formula Link Help

Originally Posted by 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)
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)[/

3. 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)

4. ## Re: Formula Link Help

Originally Posted by 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)

=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.

5. 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?

6. 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

7. Originally Posted by 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

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.

8. Originally Posted by 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
What's F\$3?

9. Originally Posted by just_jon
Originally Posted by 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
What's F\$3?
Column specifier for the lookup table. The original post mentioned C\$3 (for that matters).

10. 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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•