+ Reply to Thread
Results 1 to 5 of 5

Is it possible with a dynamic workbook reference

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    34

    Is it possible with a dynamic workbook reference

    Hi!
    Is it possible to have a dynamic workbook reference? like: 'C:\user\me\[cell value.xlsx]Sheet1'!$A1$:$A$10
    I have tried to split it like ="'C:\user\me\["&cell value&".xlsx]Sheet1'!$A1$:$A$10" among others. I have tried to use INDIRECT, but I simply cant get it to work! Is it at all possible?
    If it is, how I'm I supposed to wirte it?
    Thanks!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Is it possible with a dynamic workbook reference

    Try this:

    =INDIRECT("'C:\user\me\["&A1&".xlsx]Sheet1'!$A1$:$A$10")
    Where A1 contains the workbook name.

    Note that INDIRECT does not work on closed workbooks.

  3. #3
    Registered User
    Join Date
    06-04-2014
    Posts
    34

    Is it possible with a dynamic workbook reference

    Thanks for your help, but I need it to be able to read from closed workbooks. Forgot to mention that, sorry.
    Last edited by AliGW; 07-09-2019 at 01:25 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Is it possible with a dynamic workbook reference

    Then you will have to figure out a different way of going about this.

    One way, perhaps, might be to pull over the information from the different workbooks into the main workbook using formulas that can pull from closed workbooks such as INDEX.

    Then, once you have the data in your main workbook, you can pull from that instead of directly from the closed workbook.

  5. #5
    Registered User
    Join Date
    06-04-2014
    Posts
    34

    Re: Is it possible with a dynamic workbook reference

    Quote Originally Posted by 63falcondude View Post
    Then you will have to figure out a different way of going about this.

    One way, perhaps, might be to pull over the information from the different workbooks into the main workbook using formulas that can pull from closed workbooks such as INDEX.

    Then, once you have the data in your main workbook, you can pull from that instead of directly from the closed workbook.
    The reference is for index and match, the problem was that it didn't read the workbook name from the cell. However, I solved it by Writing a macro that replaced "dummy text" in all fomulas with the name of the workbook I want to refer to. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 11:05 AM
  2. [SOLVED] Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  3. [SOLVED] Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM
  4. Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 06:05 AM
  5. Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM
  6. Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1