Posts by mfroelaz

Hi
I realize this is quite an old thread, but maybe the answer will help someone else looking to do the same thing.
The trick here would be to simply use relative references vs locked or A1 rather than $A$1

Hi,

If you're using a version of Excel with dynamic arrays, you could try something like this

=INDEX(FILTER(C1:C1000,C1:C1000<>""),SEQUENCE(COUNTA(C1:C1000)/2,2))
Or,
=LET(_rng,C1:C1000, INDEX(FILTER(_rng,_rng<>""),SEQUENCE(COUNTA(_rng)/2,2)))