Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Formula Link Help

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237

    Default Formula Link Help

    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. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Formula Link Help

    Quote 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)[/
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237

    Default

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: Formula Link Help

    Quote 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.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237

    Default

    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. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237

    Default

    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. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    Quote 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. #8
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    Quote 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?
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    Quote Originally Posted by just_jon
    Quote 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. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237

    Default

    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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com