Forum Discussion
Excel - Subtotal
Hello I would like to use the function subtotal within an Excel sheet. The function is =SUBTOTAL(103,'Page 1'!$A:$A) At the ende it would be with an offsset =OFFSET(Tabelle1!A1;...
If the goal is essentially to create a dynamic range that omits hidden and filtered rows, you can use a 365 approach that avoids OFFSET:
=LET(
rng, $A$1:$G$50000,
Col_A, TAKE(rng, , 1),
Visible, LAMBDA(element, IF(SUBTOTAL(103, element) = 1, "", "omit")),
Keep, MAP(Col_A, Visible),
FILTER(rng, Keep <> "omit")
)- Hello
Thank you very much
I would like to use it in Power Automate
and the Formula is " =OFFSET(Tabelle1!A1;0;0;SUBTOTAL(103;Tabelle1!$A:$A);7)"
but I am getting an error. It might be because that cells are text an not numbers.
Regards
JFM_12Do you generate Office Script for that?
