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.