How to have a drop-down in a cell only if certain cells have text

Question

I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2.

I figure some dependent data validation would work, but I don't know how to get it to search A2:G2 for text first. How can I do this?

Thanks in advance for any assistance.

Answer 1

Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.

For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed

Set your data validation to List and enter this formula:

=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)

The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.