Excel - Subtotal

[JFM_12]

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;0;0;SUBTOTAL(103;Tabelle1!$A:$A);7)

The rows are dynamic but the columns are fixed.

But Excel regards it as text and not as formula.

Is this a problem with the Excel Sheet

Regards

JFM_12

[#2]

@JFM_12 

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")
)

[#3]

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_12

[#4]

@JFM_12 

Do you generate Office Script for that?