Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range error
I am naming a range based on the output of two ADDRESS() formulas, using the
format =INDIRECT(A1):INDIRECT(A2), where A1 and A2 are outputs of my ADDRESS() formulas. This seems to work, except that when I try to call the named range for a formula, it crashes excel after the first or second try. For example, if I type in SUM(range_name) it crashes about 8 times out of 10; in the 2 instances it doesn't crash, it crashes when I enter COUNT(range_name) in another cell. What gives? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range error
I think you're asking for the technical reason this fails? I don't have the
definitive answer. I suspect that it is the use of INDIRECT twice in defining the 'refers to' property of the named range. But I haven't been able to find a reference to a rule that says you can't do it - there's very little written about defining named ranges using anything other than a regular range reference of some type without anything added like INDIRECT(). I can tell you that I tried it and it failed immediately for me when I tried a SUM() on a named range defined like that. Of course, using =SUM(INDIRECT(A1):INDIRECT(A2)) worked just fine. "Antman" wrote: I am naming a range based on the output of two ADDRESS() formulas, using the format =INDIRECT(A1):INDIRECT(A2), where A1 and A2 are outputs of my ADDRESS() formulas. This seems to work, except that when I try to call the named range for a formula, it crashes excel after the first or second try. For example, if I type in SUM(range_name) it crashes about 8 times out of 10; in the 2 instances it doesn't crash, it crashes when I enter COUNT(range_name) in another cell. What gives? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update
I've worked around the problem by using just one INDIRECT() formula in my
range definition, referring to a cell where I've concatenated ADDRESS(1) & ":" & ADDRESS(2); this seems to work. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update
A+ for ingenuity.
I apologize for not offering that as a solution - I thought you were just looking for the "why" of it not working the way you had it set up initially. "Antman" wrote: I've worked around the problem by using just one INDIRECT() formula in my range definition, referring to a cell where I've concatenated ADDRESS(1) & ":" & ADDRESS(2); this seems to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting unique entries and assigning it to a named range | Excel Discussion (Misc queries) | |||
copy named range to powerpoint | Excel Worksheet Functions | |||
Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions |