Forum Discussion

Baccma's avatar
Baccma
Copper Contributor
Oct 17, 2021
Solved

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 ...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 18, 2021

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.

Baccma's avatar
Baccma
Copper Contributor
Oct 18, 2021

Riny_van_Eekelen 

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.  

Resources