• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning random text from a column


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

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