Is there a way to spill fill a column based on count of nonblank cells in adjacent column in Excel?

Question

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.

Answer 1

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)))<>"")