Returning random text from a column

[JDC8457]

I have workbook with 125 pages. Each page contains a uniform set of data.

Each page also contains a column of 100 cells that is blank except for 1 cell where there is a text entry. The cell with text can be anywhere in the column and the text can be different across pages

I've tried to write a formula to put in row 102 that captures the contents of the populated cell in the column above. No luck.

Any help is appreciated. Thx

 

[Peter Bartholomew]

For a single sheet
= CONCAT(TextColumn)
or
= VLOOKUP("ZZZ*", TextColumn, 1)
should pick up a single text entry.
= FILTER(TextColumn, TextColumn<>"")
is also an option in MS365.

 

[JDC8457]

THANKS!