Forum Discussion
Dynamic arrays and filters
I have an Excel table (which is created from third party data via Get & transform.
This is then further processed using a dynamic array.
If however I filter the table, the dynamic array still uses the full unfiltered table.
How can I get a dynamic array to work only the filtered entries and ignore those which are hidden by the filter?
Baccma Not sure I follow. Added an example in your sheet. Isn't that what you described?
5 Replies
Baccma It could work if you add a column that checks if the row is visible (1) or not (0), using the AGGREGATE function (function number 3 for COUNTA, option 5 to ignore hidden rows. Then, use FILTER to create a dynamic array of only the visible rows from the table. The attached file contains a simplified example.
Riny_van_Eekelen The furthest I managed to get using the AGGREGATE approach was to set up a helper table using a dynamic array, then add an aggregate column to that using INDIRECT to point to the original table - see attached example. However AGGREGATE does not seem to work with dynamic arrays and so, if I add an extra row to the Input table, the column containing the AGGREGATE function in the dynamic array does not expanded. I have also tried the old {} approach and that does not work either. In the example, add a row to the top input table and you will see that whilst columns A and B of the output table expand properly, column C does not.
I therefore hit a brick wall with that approach and any suggestions would be hugely appreciated.
Baccma Not sure I follow. Added an example in your sheet. Isn't that what you described?
Thanks I am aware of that approach but it is cannot be used here. That's why I made the point that the input is from table created from "Get and transform". It is not possible to add a column to this which shows whether the rows in this table are hidden or visible.
It would be great if you could use G&T to add a formula column, but if you try (even starting the text in the column with an = sign) Excel still interprets the column as a text column and fails to evaluate it.
