Forum Discussion
Dynamic arrays and filters
Baccma Not sure I follow. Added an example in your sheet. Isn't that what you described?
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?
- Ah, now that is interesting. When I added the AGGREGRATE column to the Get & Transform output, it got deleted when the G&T refreshed, It doesn't in your example. Now I need to find out why - the actual spreadsheet is much more complex obviously and the problem might be in some of the code. But thanks for clearing that point up. It looks as if I can get the visible column to stay where it is meant to be, the problem's solved. Thanks again.
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.
