Hi, is there a way to have a formula spill a range that is expanding?
Say, the range A1:A3 is populated by =SEQUENCE(3) and now want a formula in C1 that spills into C1:C3 in the following way:
C1 = range A1
C2 = range A1:A2
C3 = range A1:A3
and be able to work with these ranges by wrapping other functions around them in the C1 formula that spills down.
e.g. an expanding sum range that is not involving a spill formula would be:
=SUM(A$1:A1) and then I drag that formula down.
But how to replicate this result with a spill function that uses A1#?
Hope it's clear what I am trying to do.
Any ideas much appreciated.
Thanks
Say, the range A1:A3 is populated by =SEQUENCE(3) and now want a formula in C1 that spills into C1:C3 in the following way:
C1 = range A1
C2 = range A1:A2
C3 = range A1:A3
and be able to work with these ranges by wrapping other functions around them in the C1 formula that spills down.
e.g. an expanding sum range that is not involving a spill formula would be:
=SUM(A$1:A1) and then I drag that formula down.
But how to replicate this result with a spill function that uses A1#?
Hope it's clear what I am trying to do.
Any ideas much appreciated.
Thanks