Structured reference and offset

[Jungan]

I am using offset and structured refs in a table to create dynamic arrays.

=OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1)

My intention with this formula is to create an array that grows in height for each row. But the return value is just a single value.

ROW([@number])-ROW(Table2[[#Headers];[number]])

this counter works fine on its own in a separate column in the table. It also retunrs the right value when nested which I verify with F9. But still the offset returns a single value array for all rows in the table?!

 

[Jungan]

Interesting, I think they are facing something similar.

 

[EmmeEnne1979]

Hello all and thanks to have accepted my subscription.

I am struggling to find a solution to combine SUMPRODUCT and SUBTOTAL for a structured table.
I have a table with slicer, and I would like to get the SUMPRODUCT based on slicer selection.
By looking for a solution, I got this formula example:

=SUMPRODUCT(
SUBTOTAL(3,OFFSET('Raw Data'!F2:F150,ROW('Raw Data'!F2:F150)-ROW('Raw Data'!F2:F150),0)),
('Raw Data'!F2:F150=5)+0)

and I tried to apply this in my case. So, step by step:

- ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])-MIN(ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])) ==> This Works
- The next step is to apply the OFFSET function ==> now I am blocked

Could you help me?

Many thanks in advance

 

[Joe4]

Hello all and thanks to have accepted my subscription.

I am struggling to find a solution to combine SUMPRODUCT and SUBTOTAL for a structured table.
I have a table with slicer, and I would like to get the SUMPRODUCT based on slicer selection.
By looking for a solution, I got this formula example:

=SUMPRODUCT(
SUBTOTAL(3,OFFSET('Raw Data'!F2:F150,ROW('Raw Data'!F2:F150)-ROW('Raw Data'!F2:F150),0)),
('Raw Data'!F2:F150=5)+0)

and I tried to apply this in my case. So, step by step:

- ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])-MIN(ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])) ==> This Works
- The next step is to apply the OFFSET function ==> now I am blocked

Could you help me?

Many thanks in advance

You would probably be much better off posting your question to its own thread, instead of posting to a thread that is over 8 years old.
Then it will show up on the "Unanswered threads" listing and have a lot more people seeing it.

 

[EmmeEnne1979]

You would probably be much better off posting your question to its own thread, instead of posting to a thread that is over 8 years old.
Then it will show up on the "Unanswered threads" listing and have a lot more people seeing it.

Will do. Thanks