Forum Discussion
how to refer to a dynamic array in INDEX
I am doing a task where I need to look up the number of procedures from Col D for certain year (Col A), location (Col B) and a type of procedure (Col C) and put them into a table on a separate sheet....
It appears from the screenshot you posted that you are using Excel Tables.
If you have excel 2007 or any version 2010, 2013, 2016 or 365 then it would be incorrect to use these extra lines and referencing the whole column inside your formula which will suck up the processor with heavy calculating millions of rows.
Instead you could use the table structural references inside your formulas and one of features of Excel Tables is fulfill the requirement of dynamic range meaning that if your table expands or collapses, your formulas will encompasses the revised range in the tables without the need to modify the formula.
so instead of using this OFFSET(Data!$D$1,,,COUNTA(Data!D:D))
use the Table column reference.
If you have excel 2007 or any version 2010, 2013, 2016 or 365 then it would be incorrect to use these extra lines and referencing the whole column inside your formula which will suck up the processor with heavy calculating millions of rows.
Instead you could use the table structural references inside your formulas and one of features of Excel Tables is fulfill the requirement of dynamic range meaning that if your table expands or collapses, your formulas will encompasses the revised range in the tables without the need to modify the formula.
so instead of using this OFFSET(Data!$D$1,,,COUNTA(Data!D:D))
use the Table column reference.
Yes, I did. It works, thank you.
