+ Reply to Thread
Results 1 to 2 of 2

Is there a way to work around the INDIRECT function for dynamic named ranges?

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Is there a way to work around the INDIRECT function for dynamic named ranges?

    Hi, I have a workbook with dynamic named ranges. If I use = INDIRECT(A1&"_Name"), then it will fail #REF!, here A1&"_Name" gives the dynamic name.

    I did some search on the internet and confirmed that INDIRECT() does not work for dynamic named ranges.

    So what can I do for this case?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Is there a way to work around the INDIRECT function for dynamic named ranges?

    Works for me
    Insert the numbers 1 to 5 in A1:A5
    Define this as a named range as "abc"
    in C1 put SUM(A1:A5), result 15
    in C2 put SUM(abc) normal defined range, result 15

    Now
    In B1 put "a"
    in C3 put =SUM(INDIRECT(B1&"bc")) reference defined range using INDIRECT result 15

    INDIRECT(A1&"_Name") will obviously produce an #REF error (perhaps unless it's a single cell) as will INDIRECT(A1:A5)
    It's the what you do with that INDIRECT(...) that produces the result, e.g. SUM(INDIRECT(...))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ 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. Replies: 3
    Last Post: 08-12-2015, 10:57 AM
  2. Replies: 9
    Last Post: 04-01-2015, 04:23 PM
  3. Offset and Indirect with Dynamic Named Ranges
    By nickmangan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2014, 10:42 PM
  4. Dynamic Named Ranges and Indirect Data validation INCOMPATIBLE?
    By css0911 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2013, 06:17 AM
  5. Replies: 14
    Last Post: 10-02-2013, 07:39 AM
  6. Indirect Vlookup List and Dynamic Named Ranges - Is there a workaround
    By Jennasis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 06:57 AM
  7. Help to adapt Formula syntax to work with Dynamic Named Ranges
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-28-2005, 08:06 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