+ Reply to Thread
Results 1 to 8 of 8

OFFSET(INDIRECT(ADDRESS( for dynamic external workbook reference

  1. 06-23-2022, 04:12 PM #1
    syntaxerr
    syntaxerr is offline
    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
    Reply With QuoteRegister To Reply
  2. 06-23-2022, 05:11 PM #2
    6StringJazzer
    6StringJazzer is online now
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    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]
    Reply With QuoteRegister To Reply
  3. 06-24-2022, 02:12 AM #3
    syntaxerr
    syntaxerr is offline
    Registered User
    Join Date
    06-23-2022
    Location
    Munich
    MS-Off Ver
    Microsoft365
    Posts
    3
    I‘m aware of that. That‘s 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!
    Reply With QuoteRegister To Reply
  4. 06-24-2022, 04:02 AM #4
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    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



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  5. 06-24-2022, 04:14 AM #5
    syntaxerr
    syntaxerr is offline
    Registered User
    Join Date
    06-23-2022
    Location
    Munich
    MS-Off Ver
    Microsoft365
    Posts
    3
    Thanks Glenn, that works!
    Reply With QuoteRegister To Reply
  6. 06-24-2022, 04:17 AM #6
    GlennUK
    GlennUK is offline
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    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.
    Reply With QuoteRegister To Reply
  7. 06-24-2022, 04:19 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    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" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 06-24-2022, 04:39 AM #8
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. Dynamic workbook reference, using Indirect on difference sheet, refering another file~
    By molittlemo in forum Excel Formulas & Functions
    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. Excel 2007 : INDIRECT, ADDRESS and an external spreadsheet
    By samtoucan in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1