Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Referring to Filenames in Formulas (Excel 2K)

    Hi,

    I have a working formula which I am having difficulties with because I want to remove the filename referenced in it and store it separately for easier updating on an annual basis. The current working formula is:

    =VLOOKUP(S3,'[Sowreg02-2003.xls]SOW Register'!$A$5:$D$200,4,FALSE)

    I would like to separate the filename, or filename and sheetname and store them in a separate cell but am unable to get a reference similar to below to work satisfactorily:

    Cell U2: "Sowreg02-2003.xls" (No Quotes) - Changes each year.

    My initial attempts to use this have been along the lines of what is below:

    Cell U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    and this currently gives #REF! - an invalid reference.

    Any suggestions as to how I can get this to work?

    Regards,

    Peter Moran
    Two heads are always better than one!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    When using INDIRECT with an external file, the external file MUST be open.

    Is the "sowreg-02-2003.xls" file open when the other file is calling it?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    Yes, the file is open Steve. Thanks for the input.

    Also altered the formula in the previous post from:

    Cell U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    to:

    Cell U4: =INDIRECT("'["&U2&"]SOW Register'!"&$A$5)

    Still getting #REF!

    Do single quotes (apostrophes) have to be handled specially in strings?

    Regards,

    Peter Moran
    Two heads are always better than one!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    If you get rid of the INDIRECT what is your string? It sounds like you have a mistake in your string.
    There is no problem with using single quote in a string.

    I can NOT duplicate your problem. The formulas as you have posted them seem to be fine.

    I created a file named "Sowreg02-2003.xls"
    with a sheet named "SOW Register"
    And a table of example things in $A$5:$D$200

    I put a value from col A in S3 of a "test book" and got the proper result from:
    =VLOOKUP(S3,'[Sowreg02-2003.xls]SOW Register'!$A$5:$D$200,4,FALSE)

    I put in cell U2 of test book the filename:
    Sowreg02-2003.xls

    Then used the formula in U4:
    =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    and retrieved the contents of cell A5 from sheet "SOW Register" in filenamed: "Sowreg02-2003.xls"
    as expected:

    If I use your "new" change:
    =INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
    I need to put a cell reference in cell A5 of the testbook to pull up a cell

    If I put the text:
    "D4" in cell A5 of the test book I retrieve the contents of cell D4 from sheet "SOW Register" in filenamed: "Sowreg02-2003.xls"

    If I do NOT have anything in A5 or it is NOT a valid cell reference, I will get the #ref! error. If the file "Sowreg02-2003.xls" is closed, you will also get the #ref! error since indirect will NOT work on closed files.

    The VLOOKUP formula (which is a DIRECT reference) will work fine even if "Sowreg02-2003.xls"
    is closed!

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    For anyone interested I have found a solution:

    U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    Note that the cell address at the end of the string must be inside the quotes - I can't see any other solution that works.

    Peter Moran
    Two heads are always better than one!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    $A$5 can be outside the quotes:

    =INDIRECT("'["&U2&"]SOW Register'!"&$A$5)

    but then, of course the INDIRECT kicks in - it evaluates the contents of cell A5 on the current sheet and concatenates this with the string before it. So if U2 contains "Sowreg02-2003.xls" and A5 contains "P3", the formula will return the contents of '[Sowreg02-2003.xls]SOW Register'!P3, and if A5 contains 17, the formula will return #REF since it tries to return the contents of '[Sowreg02-2003.xls]SOW Register'!17 which is not a valid address.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    I am confused:
    What is different about this NEW one from <post#=267846>post 267846</post#>:
    =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    and the one from your ORIGINAL <post#=267750>post 267750</post#>
    =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    They both look the same to me.

    As I mentioned in my <post#=267842>post 267842</post#> (and Hans reiterated in <post#=267851>post 267851</post#>, the formula with the cell OUTSIDE the reference works fine:
    =INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
    though you have to (in the file with this formula, have a CELL REFERENCE in cell A5 since it is INDIRECTLY referenced (just like the filename is indirectly referenced)

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    Hi Steve and Hans,

    Thanks for your input, most appreciated. I will try and clarify below.

    I started with:

    =VLOOKUP(S2,'[Sowreg02-2003.xls]SOW Register'!$A$5:$D$200,4,FALSE)

    and wanted to remove the filename from this and a number of other formulas and make it an external reference for more obvious updating at the end of a financial year.

    Given some initial problems, I tried to simplify my problem and was eventually successful, thanks to your assistance, with:

    =INDIRECT("'["&U2&"]SOW Register'!$A$5")

    which had the external reference I wanted and showed me the contents of cell A5 in Sowreg02-2003.xls.

    However I have been unable to successfully extrapolate that to my original formula, as neither:

    =VLOOKUP(S2,INDIRECT("'["&U2&")]SOW Register'!$A$5:$D$200"),4,FALSE) - gives #REF!

    nor

    =VLOOKUP(S3,INDIRECT("'["&U2&")]SOW Register'!Register"),4,FALSE) - gives #REF! - with Register a range defined A5200.

    will work as I want.

    Any further ideas?

    Many thanks,

    Peter Moran
    Two heads are always better than one!

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Referring to Filenames in Formulas (Excel 2K)

    Hi again,

    Whoops - found my problem - incorrect string with a surplus ")" after &U2&".

    =VLOOKUP(S2,INDIRECT("'["&U2&")]SOW Register'!$A$5:$D$200"),4,FALSE)

    now:

    =VLOOKUP(S2,INDIRECT("'["&U2&"]SOW Register'!$A$5:$D$200"),4,FALSE)

    This now works exactly as intended.

    Regards,

    Peter Moran

Posting Permissions

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