Switch validated list items on or off

This can be done with a secondary set of helper cells. I would use two extra columns.

Let's assume column S and T are free. S will become an INDEX of rows with no complete date. So in S2:

=IF(AND(A2<>"", B2=""), N(S1)+1, N(S1))

Copy that down. This formula will NUMBER the rows that have a value in column A but no value in column B. You will see repeated numbers for the rows that have a completion date. Our next step will skip those repeats.

Now in T we will create a NEW list of projects using the index in column S.

T2: =IF(ROWS($A$1:$A1)>MAX(S:S), "", INDEX(A:A, MATCH(ROWS($A$1:$A1), S:S, 0))

Now create your dropdown list on sheet1 using column T instead of A.

Post a sample workbook if you need assistance applying this suggestion.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.