Going for the record of the shortest post...
If have loads of tabs and you need to jump quickly from one end of the workbook to the next, hold down the control button when clicking on the direction arrows next to the tabs at the bottom of Excel.
Wednesday, 17 October 2018
An Indirect reference to a named Dynamic Range doesn't work
Excel doesn't like you getting too clever clever. There's only so much dynamism she'll take.
I use the Indirect function quite a lot where I want the flexibility to expand a model for more and more cases. So I need to copy existing formulas to work for the next case and if the cell references are indirect I don't have to update these each time I or my users copy formulas.
If you make an Indirect reference to a simple static Named range reference, then it all works fine as Excel performs her usual magic.
If you make an Indirect reference to a dynamic Named Range where the range is dependant on the result of a formula, usually an Offset formula with a CountA thrown in, then you'll be out of luck and scratching your head as to why you are left staring at a REF!. Dunno why she objects. I'm sure there's a good reason. What's a bit annoying is she never tells you the dynamic Named Range is the problem. Hence why I'm jotting it down here.
So is there a way round it? Well you can use a different sort of dynamic named range, a more formal sort, an Excel Table.
It can have the same name and so does the same trick. They both play nicely with the Index function allowing you to dynamically reference any value in these ranges. The advantage of a table is you can reference its column by name.
=Index(SomeTable[ColName], 1) - gets the 1st cell in the ColName column
You don't have to specify the column name of course. This works for both Tables and Named Ranges and may suit a more dynamic approach.
=Index(SomeTable, 1, 1) - gets the 1st cell in the 1st column.
You can also, like with a Table, specify just a column.
=Index(SomeNamedRange, ,1) - sets up a reference for the entire 1st column by omitting the row.
Of course in the same scenario with a Table, you don't even need the index function!
I use the Indirect function quite a lot where I want the flexibility to expand a model for more and more cases. So I need to copy existing formulas to work for the next case and if the cell references are indirect I don't have to update these each time I or my users copy formulas.
If you make an Indirect reference to a simple static Named range reference, then it all works fine as Excel performs her usual magic.
If you make an Indirect reference to a dynamic Named Range where the range is dependant on the result of a formula, usually an Offset formula with a CountA thrown in, then you'll be out of luck and scratching your head as to why you are left staring at a REF!. Dunno why she objects. I'm sure there's a good reason. What's a bit annoying is she never tells you the dynamic Named Range is the problem. Hence why I'm jotting it down here.
So is there a way round it? Well you can use a different sort of dynamic named range, a more formal sort, an Excel Table.
It can have the same name and so does the same trick. They both play nicely with the Index function allowing you to dynamically reference any value in these ranges. The advantage of a table is you can reference its column by name.
=Index(SomeTable[ColName], 1) - gets the 1st cell in the ColName column
You don't have to specify the column name of course. This works for both Tables and Named Ranges and may suit a more dynamic approach.
=Index(SomeTable, 1, 1) - gets the 1st cell in the 1st column.
You can also, like with a Table, specify just a column.
=Index(SomeNamedRange, ,1) - sets up a reference for the entire 1st column by omitting the row.
Of course in the same scenario with a Table, you don't even need the index function!
Subscribe to:
Posts (Atom)