Into my index formula I would like to pull the name of the "referenced range" from a nearby cell (V45) - but using the following formula is not working: =INDEX(INDIRECT(V45),MATCH($L52,JobID,0),1). (The formula works fine if I simply put in the name of the named range (ie, replace "indirect(V45)" with my range of one column named "DesignStart".)
I found this thread, which gives me some clues, but I fear not enough:
http://www.ozgrid.com/forum/viewthre…vascript:void(0);
Any thoughts?
Formulas : Named Ranges in Index function
-
-
-
Hmm. I just tried using Indirect with a defined name and it worked correctly. Here was my formula
=INDEX(INDIRECT(B5),C1)
where B5 had the defined name. What happens when you entered your formula? Try it without the Match function to isolate the problem. -
Even when I strip out the second half of the equation to isolate the problem, I still get a "#REF!" error in the cell. But here's a clue: it works fine when I use a named range created on the same sheet and errors out when I use a name for a range (which lies on another sheet). So, I guess the question now is, how do you reference named ranges on a different sheet from which they were created?
-
Hmm. I had no problem with indirect even though the name referred to a range on another sheet. I don't know why it is a problem for you. Is it still the case that the Index works OK when you use the defined name referring to another sheet directly instead of indirectly?
-
Yes - the Index formula works fine when I directly refer to a named range (off the present sheet). But I've just found that it's got something to do with referring to dynamic ranges indirectly. They create an error - though referring directly (to a dyn range) is fine. Any idea why "indirect" and "dynamic" don't play nice?
-
-
I'm not sure if you have seen this post on the subject.
It was never solved, however it may provide some insight in to why these two functions don't sit well together.
Hope it helps,
http://www.ozgrid.com/forum/viewthread.php?tid=6001
Will
-
Hi Guys
Here is a work-around. Go here
http://www.ozgrid.com/VBA/range-name-address.htmand create the Custom Function: RangeNameAddress Now place the function into the cell you will reference with the INDIRECT function; Eg
=RangeNameAddress(MyDynaRange,FALSE)
-
I shoulda known "the man" and his bag o' tricks would pull through!
Thanks for all the help, guys - much appreciated.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!