Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How do I reference a filename inside a cell from a formula

  1. #1
    Guest

    Default

    Hi,

    I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

    Example

    A1: filename.xls

    B2: =+'filename.xls'!C1
    C2: =+'filename.xls'!C2

    I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

    I don't know macros so I haven't touched on it.
    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 01:34, Anonymous wrote:
    Hi,

    I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

    Example

    A1: filename.xls

    B2: =+'filename.xls'!C1
    C2: =+'filename.xls'!C2

    I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

    I don't know macros so I haven't touched on it.
    Thanks.
    see if this helps out :

    in B2 enter : =INDIRECT(""&A1&"!C1")
    in C2 enter : =INDIRECT(""&A1&"!C2")

    I used INDIRECT earlier today with the help of one of Aladin's postings on the old board.


    :: Pharma Z - Family drugstore ::

  3. #3
    Guest

    Default

    Hello Chris,

    Sorry for the delayed post as I only got to read your reply only today. I've tried your suggestion and after figuring out the order of the single quote and double quotes, it initially gave a #REF error. Then checking the help file regarding INDRECT, I understood that you have to open the external reference and so when the file was opened, it worked!

    The problem now is that the files I am working with are daisy chained. Ie. balances from JAN02.XLS are carried over to FEB02.XLS, and so on. If I don't open the previous files, then I'll get reference errors. Any suggestions?

    Thanks for your time and help.

  4. #4
    Guest

    Default

    Hi Chris,

    Its ok with my problem now. After I've read Brian from Maui's post Re: Funny story, it just solved my problem. I just have to Edit/Replace/Find/Replace with!

    Thanks again.

  5. #5
    Guest

    Default

    I have been trying to get this to work but with filename and sheetname. Is it possible?

    On 2002-02-22 13:13, Chris Davison wrote:
    On 2002-02-22 01:34, Anonymous wrote:
    Hi,

    I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

    Example

    A1: filename.xls

    B2: =+'filename.xls'!C1
    C2: =+'filename.xls'!C2

    I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

    I don't know macros so I haven't touched on it.
    Thanks.
    see if this helps out :

    in B2 enter : =INDIRECT(""&A1&"!C1")
    in C2 enter : =INDIRECT(""&A1&"!C2")

    I used INDIRECT earlier today with the help of one of Aladin's postings on the old board.



  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    [quote]
    On 2002-03-01 04:31, Anonymous wrote:
    I have been trying to get this to work but with filename and sheetname. Is it possible?
    [quote]

    Lets say that you have in

    A1: [file1.xls]Budget
    A2: [file1.xls]
    A3: file1.xls
    A4: Budget

    =INDIRECT("'"&A1&"'!H10")

    will retrieve the value in H10 from sheet named Budget in file1.xls.

    =INDIRECT("'"&A2&A4&"'!H10") will do the same.

    =INDIRECT("'["&A3&"]"&A4&"'!H10") will do the same.


Some videos you may like

User Tag List

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
  •