Unable to satisfy multiple conditions in data validation

[ishpahuja]

Hi guys, need some help with Excel formula and/or data validation.
I have two columns with multiple rows. For each row, the first column will have only two options "Yes, No", which I gave options using data validation by writing "yes, no" in another hidden sheet.
Now for second column, there are two conditions for each of the row item.
1. The cell in 2nd column should get activated only if the response in the corresponding cell in 1st column is "Yes", otherwise nothing is allowed to enter and it should have gray color.
2. When the response in a cell in 1st column is "Yes", the corresponding cell in 2nd column should have two options only to enter i.e. Yes, No (No free text).

Sample situation below in image.

I was able to use data validation for the 2nd column but I could only apply one of the two conditions. Can you help what shall I do so both conditions are satisfied?

 

[KRice]

For the 1st column, I would recommend typing the list directly in the Data Validation "Source" field, like this:

Then for the 2nd column, which would be a dependent list (depending on the 1st column contents), I would set up helper columns to construct the list that should be used by Data Validation (for the 2nd column). The formula for those helpers is: =IF(A2="Yes",{"Yes","No"},"")
You may need to enter this as an array formula with Ctrl-Shift-Enter (whereupon curly brackets will automatically surround the formula) and drag to the right to cause the Yes and No to spill across two columns (not necessary in Excel 365). Then, because these helper columns contain either 0 or 2 entries, to force the dropdown list to display only the items in the helper columns and no extra blank cells, I use a formula with OFFSET and COUNTA to construct a dynamically sized list. =OFFSET(D2,,,,COUNTA(D2:E2))

Finally, conditional formatting is used to fill the 2nd column with gray when "No" appears in the 1st column. There is only one issue...if used as intended and no second edits are made to the cells, there is no issue. However, If the 1st column is selected as "Yes" and then the 2nd column choice is made (either Yes or No), and then one returns to the 1st column and chooses No, the existing Yes/No in the 2nd column will remain there unless physically deleted or the dropdown in the 2nd column is chosen again to select the only option (a blank)...see this demonstrated in row 5.

The helper columns are then hidden or otherwise positioned out of the way.

MrExcel_20220831.xlsx
	A	B	C	D	E
1	Requires Technology	Estimate Completed		Helpers
2	Yes	No		Yes	No
3	No
4	Yes	Yes		Yes	No
5	No	No
Sheet5

Cell Formulas
Range	Formula
D2:E2,D4:E4,D3,D5	D2	=IF(A2="Yes",{"Yes","No"},"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B5	Expression	=$A2="No"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	Yes,No
B2:B5	List	=OFFSET(D2,,,,COUNTA(D2:E2))

 

[ishpahuja]

Thank you so much!

 

[KRice]

You are welcome...I'm happy to help.