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

Thread: Reference a filename created with a formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Reference a filename created with a formula

    I'm trying to reference a filename and path that I've created with a formula. I can't get it to work either in a formula or with a Macro.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    For what purpose?
    How/where exactly are you trying to reference it?

    Perhaps you are looking for the INDIRECT formula.
    See: https://exceljet.net/formula/dynamic-workbook-reference
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    I've tried the INDIRECT formula. It gives me back the "formula", it displays the FORMULA complete with the equal sign, but not the formula result value. I tried to do Text To Columns to convert that response to a "working" formula, but that did nothing.

    What I'm trying to do is to reference a file that contains information needed in the report that I am working in. The file that I am trying to access has a filename based on a reference date.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    Will you have the file you are referencing open or close at the time you are running this?
    What does the formula you built look like (please post it here)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    The formula that "builds" the filename is:

    ="P:\Finance\Bill of Materials\[Routings "&YEAR(Sheet1!$A$2)&"]CS "&YEAR(Sheet1$A$2)& Routings.xls" - This returns the correct filename that I am looking for.

    I don't have a copy of the original INDIRECT formula that I used, but when I enter =INDIRECT(A1&"!"&B1) where cell A1 contains the formula above and cell B1 contains the Cell reference that I want, I get a #REF error.

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    The file that I am referencing is NOT open.

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    The file that I am referencing is NOT open.
    OK. That is the problem. INDIRECT does not work on closed workbooks.

    There is an Excel AddIn called MoreFunc that has an INDIRECT.EXT function that will allow you to do this.
    See: http://www.ashishmathur.com/tag/indirect-ext/

    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    Thank you, I will look for that Add-in.

    The more I'm working on this project, I'm thinking that I'd really like to be able to open the referenced file in a Macro. I'm not having much luck with that, though. Can you help me? How can I reference that filename in the Workbooks.Open command and not get a debug error?

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    Can you post the code you have for opening the file, and we can help you clean it up?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    This is the code I have:

    (Sorry, I can't figure out how to get a "Paste" block to copy the code into)

    [Dim PeriodBook As Workbook
    Dim ThisYear As Integer
    PeriodBook = Range("A2").Value
    ThisYear = Range("B2").Value
    Workbooks.Open Filename:= _
    "PeriodBook"
    Cells.Find(What:="ThisYear").Activate
    ActiveCell.Offset(1,12).Select
    Selection.Copy
    Windows("PKG PROD NUMBERS TEST2.xls").Activate
    Range("A4")Select
    ActiveSheet.Paste]

    Cell A2 contains the filename P:\Finance\[PERIOD END DATES 2002-2020.xls]Period Dates
    this "value" is generated by a formula.
    Cell B2 contains the year 2019, again the value is determined by a formula.

    I am also having trouble with the Find command in this code.

    Thank you.

Some videos you may like

User Tag List

Tags for this Thread

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
  •