INDIRECT and Dynamic Ranges
This isn’t a new issue, but it’s new to me. Charlie, a loyal reader, was trying to use INDIRECT with a dynamic range name and kept getting errors.
=MAX(INDIRECT("List2"))
returns the #REF! error. It appears to be a limitation of INDIRECT (yes, another one).
One way to get around the problem is to just reproduce the dynamic range name formula in the cell.
=MAX(OFFSET(Sheet1!$B$6,0,0,COUNTA(Sheet1!$B:$B)-1))
That works, but now when you change List2, you have to remember to change this formula too, not to mention all the other formulas that use this.
Option #2 is a UDF.
=MAX(DINDIRECT("List2"))
Public Function DINDIRECT(sName As String) As Range
'It stands for Dynamic Indirect
Dim nName As Name
'Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0
'Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DINDIRECT = nName.RefersToRange
Else
DINDIRECT = CVErr(xlErrName)
End If
End Function
'It stands for Dynamic Indirect
Dim nName As Name
'Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0
'Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DINDIRECT = nName.RefersToRange
Else
DINDIRECT = CVErr(xlErrName)
End If
End Function
Gee, as simple as that function is you’d think Microsoft would have put in the program.
Does anyone have an option #3?
You should make your function Volatile as well Dick, at least to be consistent with INDIRECT().
option #3:
Instead of using
=MAX(INDIRECT(List2)),
Charlie must use:
=MAX(List2)
INDIRECT does not evaluate formulas that use worksheet functions, and if the defined name represents a formula and not a simple reference, it’ll error out. A possible Option #3 is to use XLM’s EVALUATE.
Define the name “List2″ as:
=MAX(OFFSET(…))
Define another name “Eval” as:
=EVALUATE(List2)
Call as:
=Eval
Regards,
Jason
Doh! EVALUATE is overkill in this case. Just use:
=List2
Jason
Daniel: The point is that “List2″ is created via a formula and can’t be hardcoded. Therefore, the indirect is necessary, although I left all that background out of the post.
Jason: I couldn’t get that to work. Even if it did, you would need List2 to return a scalar value and not a range reference. That would be fine if you only wanted to take the MAX of the range, but if you hand more formulas that used that range, you would need one name for every formula. Am I right on that, or am I missing something?
Thank you everyone for your help. Juan Pablo is correct. Whatever UDF is created it needs to be volatile. That is tha majic of the INDIRECT function.
I created a validated dropdown box with 4 items to choose from. Each items is a named range consisting of one column from a large Excel database. When the user selects the item the INDIRECT function reads the selection and finds the correct named range. There are multiple calculations being done; MAX, MIN, SUM, AVERAGE, etc. all calculating the selected item from the dropdown. The INDIRECT function is being used in all calculations.
I use one poor work around. The range begins at row 1 and ends at row 65536. The problem with this is wasted process time. My computer has to look through all those cells for nothing.
Thanks Dick, I didn’t get the point about creating List2 dynamicaly (perhaps because I disagree about this specific usage).
Charlie,
I disagree with the way you are adressing (!) the problem.
In my view, the select you make in the dropdown should trigger another change value (let’s call it Shift).
Even with very complex spreadsheet, the Shift can be obtained via a VLOOKUP() to an array that would determine the proper offset depending on the dropdown value.
Then your List2 name would use the Shift name (in the OFFSET function) to correctly define the proper range.
This method is perfectly scalable to any amount of ranges (doesn’t need to define one Name for each choice).
And the spreadsheet dependencies are straightforward. No call to volatile.
My two cents anyway (but it’s your call after all).
Daniel
Dick said:
The last part is not necessarily correct (about needing to change the formula when list2 changes).
As identified, indirect unfortuantely does not work with dynamic ranges, but if you have named references to a specific cell and the size of the relevant range can be determined by means of other associated data you can use indirect If you have no associated data, and cannot determine what the maximum size of that list will be, you’re out of luck.
For example, here’s a formula from one of my projects, that is then wrapped in sum, max, median, average, or “=value” statements (- and all return the correct values)
OFFSET(INDIRECT(”tournamentsCellStart_” & $A$3),RankingYearStart,0,countTournaments_Selected,1)
and the absolute reference $A$3 is determined by some means, and resolves to:
tournamentsCellStart_Chris =Tournaments!$G$3
tournamentsCellStart_Colin =Tournaments!$I$3
etc
countTournaments_Selected is determined by a set of date range conditions in named formulas.
But indirect here operates only on the (conditionally selected) starting cell for the offset function, so no problems
n
Question: Will using DINDIRECT() work, if I’m trying to use this to provide a dynamic range for data validation?
In my attempts, INDIRECT(dynamic range) doesn’t work for LIST data validation.
I also tried using DINDIRECT(dynamic range), as the argument for LIST data validation (and implementing the above code). Excel then returns the message “A named range you specified cannot be found”.
As background: The dynamic range is already defined, and can be successfully accessed via INDEX/MATCH and so on.
Hi the DINDIRECT function seems like a great solution to me but when i try to make it volatile (just by inserting application.volatile as the first line) it breaks and I get a #VALUE! error in my cell. If anyone has any advice on how to fix this i’d really appreciate it.
Thanks,
Ben
Hello,
the question is, why do you actually need INDIRECT().
Usually you want to use INDIRECT() in data validation, where the validation list depends on the choice in another cell.
I have certain success with the following:
1) Let List1 and List2 be your dynamic ranges.
2) Let List be defined as {”List1″,”List2″}
3) Let A1 be validated as List
4) Let your validation range be validated as list like this:
=CHOOSE(MATCH($A$1,Lists,0),List1,List2)
I know this doesn’t answer the question, but it serves as a workaround for one specific case. The INDIRECT() is to be avoided with calculated ranges (that do not appear in the name box). Everybody knows that INDIRECT() is to be avoided with closed workbooks and yep, this is another limitation.
Martin
Oh, sorry for the typo, of course it should read:
=CHOOSE(MATCH($B$7;List;0);List1;List2)
In addition to Martin Krals statement on the combination of Choose and Match, I want to mention the following. For the dynamic selection to work flawless, it is necessary to have the same sequence in the CHOOSE list as you have defined in the dynamic range list. If you don’t use the same sequence then the wrong dynamic range will be set.
Hi Dick,
I have used the code you gave on 01.03.2005 for the following purpose.
In cell A1 of a sheet I have a validation list containing name of 3 sheets HDFC, SBOP, SBIL. I have named ranges
Recobank=”‘”&Reco!$A$2&”‘!”
RecoAmt=Dindirect(”‘”&RecoBank&”‘!i4″)
Now the RecoAmt should result in Value contained in Cell I4 of sheet named HDFC. But it is not giving me any results. What should be the reasons ?
Regards
CA kanwaljit singh Dhunna
Hi,
Just to mention that in the above case
RecoAmt=Evaluate(”‘”&RecoBank&”‘!i4″) is working fine.
Hi Dick,
I found this post while searching for a work around to Indirect referencing a dynamic range name limitation.
I am using a scroll bar to scroll through tables based on Week/month.
I have a cell that stores the scroll bar value (C9).
I then use in another cell (C22) a string formula”Dashboard_Data_Table”&$C$9.
I am then using the following to dynamically update the data =INDEX(INDIRECT(C22),Rownum,ColNum).
The problem I was having was the Indirect Dynamic ref failure. I searched google and bang! DIndirect has resolved the problem. Thank you so much. I now have a dynamic dashboard that can scroll through different time scaled tables. This allows users to stay on one screen a push 12 months worth of data through the various dashboard charts. I am sure I would have found another work around eventually however, this was quick, simple and worked a charm.
Thanks again
devoted ddoe reader
Tim B
Hi Dick,
I found this post while searching for a work around to Indirect referencing a dynamic range name limitation.
I am using a scroll bar to scroll through tables based on Week/month.
I have a cell that stores the scroll bar value (C9).
I then use in another cell (C22) a string formula”Dashboard_Data_Table”&$C$9.
I am then using the following to dynamically update the data =INDEX(INDIRECT(C22),Rownum,ColNum).
The problem I was having was the Indirect Dynamic ref failure. I searched google and bang! DIndirect has resolved the problem. Thank you so much. I now have a dynamic dashboard that can scroll through different time scaled tables. This allows users to stay on one screen a push 12 months worth of data through the various dashboard charts. I am sure I would have found another work around eventually however, this was quick, simple and worked a charm.
Thanks again
devoted ddoe reader
Tim B
Ran into this problem today. Had no idea about the problem of dynamic ranges and indirect references. Ugh.
So, I also took a UDF approach, but decided on a simple string return:
' Takes the name of a dynamic range and returns
' the address of that name.
Application.Volatile
Dim rng As Range
' Set the range variable.
Set rng = Range(range_name)
' Return the range variable address. Also include the
' sheet name so that it will work across separate worksheets.
DRNG_ADDRESS = rng.Parent.Name & "!" & rng.Address
End Function
So, using a VLOOKUP to get the dynamic range name (which I have in ANOTHER dynamic range–don’t ask!), I can pull the name, return the DR address as a string (along with the sheet name), and use the indirect in the DV.
The formula’s pretty easy, even with the lookup:
@Scott… you may want to consider this modification to your UDF just in case the target sheet name for the defined name’s range contains one or more spaces…
' Takes the name of a dynamic range and returns
' the address of that name.
Application.Volatile
Dim rng As Range, ParentName As String
' Set the range variable.
Set rng = Range(range_name)
' Add apostrophes around sheet name if it has a space in it
ParentName = rng.Parent.Name
If InStr(ParentName, " ") Then ParentName = "'" & ParentName & "'"
' Return the range variable address. Also include the
' sheet name so that it will work across separate worksheets.
DRNG_ADDRESS = ParentName & "!" & rng.Address
End Function
@Rick: Good call! I would have rememebered after it didn’t work…!
So, with a slight modification:
@Rick - better just to put single quotes around the worksheet name ALL THE TIME. Note that worksheet names may contain the following chars.
- + , ‘
Excel wraps worksheet names containing any of these in single quotes (with embedded single quotes doubled). IOW, checking only for spaces is begging for problems.
Better still to USE BUILT-IN FUNCTIONALITY rather than reinventing wheels.
Dim rng As Range, wb As Workbook
Application.Volatile
If Not TypeOf Application.Caller Is Range Then
Exit Function ' return ""
Else
Set wb = Application.Caller.Parent.Parent
End If
On Error Resume Next
Set rng = wb.Names(tr).RefersToRange
Err.Clear
If rng Is Nothing Then Exit Function ' return ""
foo = rng.Address(0, 0, , 1)
If Not iwbn Then foo = Replace(foo, "[" & wb.Name & "]", "", , 1)
End Function
This also takes some precautions that yours doesn’t. Note the issues yours would have if called from a formula in a cell that’s not in the active workbook. Also unclear why one would need/want the $s in the result textref. Better to return “” rather than #VALUE! when the argument doesn’t refer to a name that in turn refers to a range in the calling workbook. Finally, sometimes you may want the workBOOK name too, so why not make it an option while defaulting to w/o w/s name?
@fzz… All good points! Thanks for following up with them.
@fzz: I like your solution! For my immediate purposes, it’s probably a little too much, though. If I were going to be using the fucntion across multiple workbooks, or call it from a formula, then I’d go for something like this. But my need was pretty simple, and since I’m controling the use of the function (rather than the end-user), I don’t want to over-engineer it.
But I do like your solution for its portability!