Forum Discussion

JFM_12's avatar
JFM_12
Iron Contributor
Feb 08, 2024

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;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

Admin
Formulas and Functions
Training
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 08, 2024

    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")
)
    • JFM_12's avatar
      JFM_12
      Iron Contributor
      Feb 15, 2024
      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

Resources