CODEWITHSUNDEEP
excelexcel-formula
DS_London
DS_London

Reputation: 4271

How to reference an entire column from one cell in a spreadsheet function

I am looking for a way to accomplish the following using spreadsheet formulae: the output in cell D5 should be the array from cell B5 to the last populated cell (assume the range is contiguous). Thus if text were typed into cell B10, the output would automatically expand.

enter image description here

However, I want to avoid the volatile OFFSET and INDIRECT functions.

So I have this formula in cell D5:

=UPPER(B5:INDEX(B:B,ROW(B5)+COUNTA(B5:INDEX(B:B,ROWS(B:B),,1))-1))

but this is unsatisfactory as it references the B:B column.

In effect I want a construction that returns me B:B given the cell B5 ie the equivalent of =INDIRECT("C" & COLUMN(B5),"FALSE"), but in a non-volatile format.

Any suggestions?

NB. I don't have the very latest Excel. I have LET, SEQUENCE, FILTER etc, but not LAMBDA and the functions that came with it (ie I can't do recursive LAMBDA calls).

Upvotes: 0

Views: 456

Answers (1)

Spectral Instance
Spectral Instance

Reputation: 2494

Make column B data into a table

screenshot of table illustrating structured referencing solution

the content of cell D5 can then be dragged to a different location, without the formula results changing.

Upvotes: 1

Related Questions