Hi everyone,
I tried many times to use the formula such as
Here, A1 is the name of a dynamic named range:
=AVERAGE(INDIRECT(A1))
But this always resulted in the #REF!.=OFFSET(Data!$A$1,0,0,1000,1)
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.
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
Many thanks, Pete_UK. I will have a go by using INDEX to define the dynamic range.
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.
Last edited by BNCOXUK; 09-19-2019 at 04:53 AM.
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
So the INDIRECT() function cannot be used in a dynamic named range? Both the INDEX() and OFFSET() have failed!
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.
???
=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...
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!
I even used a simple formula like: =AVERAGE(INDIRECT(E1)) to test it, but still the #REF! error.
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.
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!
Since you still haven't told me what you are trying to do with this, I'm finished playing guessing games. Good luck!!
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.
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....
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?
I am happy to close this thread now. Quite simply, INDIRECT() is not applicable to dynamic named range such as OFFSET or INDEX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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
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).
Everyone who confuses correlation and causation ends up dead.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
BNCOXUK 
Register To Reply
Bookmarks