Helper-free, non-volatile, pre-O365 selecting VISIBLE cells for other functions

[Glenn Kennedy]

Today's fun challenge...

Selecting ONLY visible cells for further processing.

We can use AGGREGATE(3,7,A2) in as a single-cell array, copied down to produce a helper column

We can use OFFSET and BYROW to do the same. However... can we come up with a FORMULA that:

a) does NOT need a helper
b) does NOT employ the volatile OFFSET function
c) does work in pre-O365 Excel products?

I've been mucking around with the AGGREGATE approach off and on for much of today, trying to coerce it into working as specified and have been completely unable to reproduce the results in columns E, F & G using something that meets ALL 3 of the above criteria.

Make your brains hurt and give it a go.

[Fluff13]

How about

Please Login or Register  to view this content.

[Glenn Kennedy]

Fluff... can I be a REAL PAIN and add a 4th criterion... dynamic array. The problem originally arose here with someone with Excel 2021. So they have dynamic arrays... but not BYROW.

Sometimes I get a bit OCD about thses things. Rep for your effort.

[Fluff13]

Initial reaction is that for a spill range, you will need to use a Lambda function, Offset or a helper column. But happy to be proved wrong.

[Glenn Kennedy]

That's my feeling, too, having wasted a (wet) Sunday afternoon trying (and failing) to crack this.

We'll see what other takers might come up with. You never know...