0

I am looking to find a way to fill a whole column with the same output, "Yes", based on the number of cells in the adjacent column.

For example, if there's data in A2:A10, I would like B2:B10 to be filled with "Yes". If more data is added to column A, I'd like column B to automatically update / spill the "Yes" into additional rows within B based on the number of entries added to column A.

I'm aware that I can do an =IF(ISBLANK()) statement for each row, but I am trying to reduce the number of formulas. I'd like to try and do this with a single formula within the top row of column B that spills down.

The value in column A can change, I'm only trying to check the number of non-blank values.

I'm using Excel / Office 365.

Wisp
  • 198
  • 1
  • 11
  • Rather than interrogate the entirety of column A (which would be very inefficient), in order to determine the last cell with data in that column it would be useful to know if the entries in that column are text, numeric or a mixture of both. – Jos Woolley Mar 21 '22 at 10:22
  • Thank you. I'm planning on using this in a few places, one of them is date only, one of them is numeric, and another is mixed with text and numeric. Thanks for your help. – Wisp Mar 21 '22 at 11:19

1 Answers1

2

This is a generic solution for column A containing mixed datatypes:

=REPT("Yes",A2:INDEX(A:A,MAX(IFNA(MATCH(IF({0;1},"Ω",77^77),A:A),0)))<>"")

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • 1
    I just put a random series of entries - some text, some numeric, some blank, some null strings - in column A, beginning in row 2. – Jos Woolley Mar 21 '22 at 11:48