data validation or specific value

[ajm]

using dependent validation, can you have a either a particular value or the dependent validation in the cell? I currently have a choice of two dependent validation lists but its not ideal. i am trying to make a model that inputs a certain value if one of the data val options is chosen but dependent drop downs for the remaining options? so, for example, the first validation list (Col A) contains A, B, C, D. If the user selects A, the adjacent cell in Col B should be blank. However, if they chose B, C, or D, they move on to the dependent validation list with other choices. The list formula for the current situation is = IF($C5="A",AList,Others) where AList is the value i want to have in D5 if option A is selected from the first drop down list, and if B,C,or D is selected, the list in named range Others is available.

ideally, i want a blank or hyphen to be inserted at D5 if A is chosen in the first list, or if B,C,or D are chosen, the user can then select from the list at Others. Can it be done? sort of like a list source formula:

=IF($C5="A"," - ",Others)

 

[KRice]

One approach is to rely on some helper cells to create the dependent list(s) that should be used for the dropdown options when either B, C, or D is selected. The helper formula is written to produce a blank when A is 1st chosen. While the blank is not actually "returned" to the 2nd choice cell (when A is the 1st choice), you could choose the blank from the dropdown list or simply ignore it since the cell already is blank.

MrExcel_20220805.xlsx
	C	D	E	F	G	H
4	1st choice	2nd		Helper
5	B	dog		cat	dog	rabbit
6	A
7	C	dog		cat	dog	rabbit
8	D	cat		cat	dog	rabbit
9	B	rabbit		cat	dog	rabbit
10	A
11	C	cat		cat	dog	rabbit
Sheet7

Cell Formulas
Range	Formula
F5:H5,F11:H11,F7:H9,F6,F10	F5	=IF($C5="A","",{"cat","dog","rabbit"})
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C5:C11	List	A,B,C,D
D5:D11	List	=$F5#

 

[ajm]

Thanks for your suggestion KRice.