+ Reply to Thread
Results 1 to 19 of 19

INDIRECT cannot be used in a dynamic named range OFFSET

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    INDIRECT cannot be used in a dynamic named range OFFSET

    Hi everyone,

    I tried many times to use the formula such as

    Please Login or Register  to view this content.
    Here, A1 is the name of a dynamic named range:

    Please Login or Register  to view this content.
    But this always resulted in the #REF!.

    Do you have similar experience like this? Apparently the INDIRECT() function cannot be used in dynamic named ranges.

    Is there a way to work around it? For my case, the dynamic named range is useful to me.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Both OFFSET and INDIRECT are volatile functions, so you can't use them together.

    However, you can use INDEX to define your dynamic range, and then you could use INDIRECT with it.

    Hope this helps.

    Pete

  3. #3
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    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

  4. #4
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Many thanks, Pete_UK. I will have a go by using INDEX to define the dynamic range.

  5. #5
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    I had a try with the INDEX function to create named range. However, this still does not work (#REF error). The example is attached here.
    Attached Files Attached Files
    Last edited by BNCOXUK; 09-19-2019 at 04:53 AM.

  6. #6
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    So the INDIRECT() function cannot be used in a dynamic named range? Both the INDEX() and OFFSET() have failed!

  7. #7
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    You need to tell us a bit more. What do you want to do with the range? Please upload some data showing us what your FINAL outcome is... not what stage 1 in the process is.

  8. #8
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    ???

    =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(1E+100,Sheet1!$A:$A))

    as RANGE1. Still no idea what you want to do with it, though...
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Hi Glenn, I did it in two steps:

    step 1): create named range as: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,200000), with the name Range1 placed in cell E1.
    step 2): refer to the named range as: =INDIRECT(E1).

    You can see step 2) has the #REF! error.

    Initially I used OFFSET to create named range which is used by the INDIRECT, but this had the same #REF! error. Pete_UK suggested me to use the INDEX to create named range, but still in this case failed!

  10. #10
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    I even used a simple formula like: =AVERAGE(INDIRECT(E1)) to test it, but still the #REF! error.

  11. #11
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    1. see Post 8.

    2. I can see WHAT you did. That was NOT my question. I asked you what you wanted to DO with the range. What happens next?? What is its purpose. So far, you have given us no idea what you want to do with it.

  12. #12
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    It is getting complicated now.

    To experts in this forum, my question is very simple: can the INDIRECT() function be used in a dynamic named range created via OFFSET or INDEX?

    Pete_UK recommended me to use the INDEX(). I am just curious how this can help to work it out. If there is no way to get it work, I just give up!

  13. #13
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Since you still haven't told me what you are trying to do with this, I'm finished playing guessing games. Good luck!!

  14. #14
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    Glenn, you can simply leave this question alone if you are unable or cross to help learners. No need to be so arrogant to an Excel starter. Everyone needs help. Hope this makes sense.

    If no one experts can give a definitive answer, I can then close this thread.

  15. #15
    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,893

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    No need for your ill temper. I am merely frustrated by your unwillingness to look at Post 8 and comment on it, and by your unwillingness to explain what you are really wanting to do with this range.

    The fact that I am here PROVES that I want to help....

  16. #16
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    I think my question had been very clear. Pete_UK understood my question from the very beginning.

    Your post 8 has nothing to to with my question about the INDIRECT() function. So I was puzzled by your answer. Read my question again please.

    Why the reference to a named range created by OFFSET() by using the INDIRECT() function resulted in the #REF! error?

  17. #17
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    I am happy to close this thread now. Quite simply, INDIRECT() is not applicable to dynamic named range such as OFFSET or INDEX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    It's not something I have tried to do in the past, but I have played about with it a bit. I thought this might work in F1:

    =INDEX(INDIRECT($E$1),ROWS($1:1),1)

    with Range1 defined as:

    =Sheet1!$A$1:INDEX(Sheet1!$D:$D,COUNT(Sheet1!$A:$A))

    but I also got the #Ref! error, although this works:

    =INDEX(Range1,ROWS($1:1),1)

    and you can copy it down. I also tried with a definition for Range1 of:

    =Sheet1!$A$1:$D$20

    and then the first formula, i.e.:

    =INDEX(INDIRECT($E$1),ROWS($1:1),1)

    also worked when copied down.

    Like Glenn, I'm not sure what you are trying to do, but I must conclude, as you have, that you can't use INDIRECT on a dynamic named range.

    Hope this helps.

    Pete

  19. #19
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: INDIRECT cannot be used in a dynamic named range OFFSET

    It is true that you can't use INDIRECT with DNRs. You can use it with tables though, or you can define a name that uses the old EVALUATE XLM function (though you have to use a macro-enabled workbook format in that case).
    Rory

+ 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. OFFSET or INDIRECT formula - not sure
    By carolynoi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2016, 07:18 PM
  2. [SOLVED] improve formula offset and indirect
    By Biff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  3. improve formula offset and indirect
    By John Contact in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  4. improve formula offset and indirect
    By John Contact in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. improve formula offset and indirect
    By John Contact in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  6. [SOLVED] improve formula offset and indirect
    By John Contact in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] improve formula offset and indirect
    By John Contact in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2005, 03:05 AM

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