+ Reply to Thread
Results 1 to 8 of 8

OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    Munich
    MS-Off Ver
    Microsoft365
    Posts
    3

    OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    Hi guys,

    I'm trying to make dynamic external workbook references. Ever quarter I have new data sheets with another quarterly (and yearly) file name and I also have several names which are saved in different folders.
    Therefore I created in F1:F3 an input field with name, quarter and year. In F7 and F8 I have the output field with correct File name and correct file path depend on my three input fields.

    Within the workbooks it's always the same table and same cells I need access to.

    I'm trying to use the OFFSET(INDIRECT(ADDRESS( function but I always receive the #REF! error code. Is there something wrong with my syntax and how do I get it right?

    The Cell I want to see for example in D16 is in the original file in D33.


    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,120

    Re: OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    I didn't look at your file yet, but if the other file you are referencing is not open, INDIRECT will return a #REF! error. Is that the situation?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    Munich
    MS-Off Ver
    Microsoft365
    Posts
    3
    Im aware of that. Thats not the issue. But thanks for the hint
    Last edited by AliGW; 06-24-2022 at 02:20 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    PROVIDING both files are OPEN:

    =INDEX(INDIRECT($F$7&"["&$F$8&"]"&$F$9&"'!1:1048576"),A16,B16)

    copied down.
    Glenn



  5. #5
    Registered User
    Join Date
    06-23-2022
    Location
    Munich
    MS-Off Ver
    Microsoft365
    Posts
    3
    Thanks Glenn, that works!

  6. #6
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    179

    Re: OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    Please also post file Peter_2022_Q1_STE.xlsx so as to be able to test situation. Forget that, I've just seen that someone else has solved it.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,026

    Re: OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    I don't think there is any need - the issue has been resolved.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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. external link to closed workbook with a cell reference in the address
    By amakepeace in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-14-2019, 02:30 PM
  2. using indirect(address(match())) in the offset function
    By redwar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2018, 03:03 PM
  3. Replies: 1
    Last Post: 12-18-2017, 06:46 AM
  4. External Workbook Link with Dynamic Cell Reference
    By mst3kr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-24-2014, 10:24 PM
  5. Replies: 4
    Last Post: 12-12-2009, 07:21 AM
  6. Dynamic External Workbook reference
    By Co-op Bank in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2005, 07:10 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