Data Validation List based on another Data validation list which selects certain value

[drewberts]

I have 2 named ranges called EleType and EleNo. EleType contains N/A,Type A, Type B, Type C and EleNo contains N/A,1,2,3,4,5,6,7,8

I have 2 data validation cells set up (A1 uses EleType and A2 uses EleNo). What I'm trying to do is automatically set N/A in Cell A2 if N/A is selected in Cell A1. If anything other than N/A is selected in A1 then I want A2 to remain blank so that the use can select from the EleNo data validation list - but at the same time I want the data validation list in A2 to not include N/A if A1 doesn't have N/A selected.

I hope I've explained that in a way that makes sense...

Thanks

 

[Fluff]

Whilst this doesn't automatically put N/A in A2, how about

+Fluff New.xlsm
	A	B	C	D	E
1	N/A		N/A	1
2	N/A		a	2
3			b	3
4				4
5
6
Main

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=EleType
A2	List	=IF(A1="N/A",C1,EleNo)

 

[deepbhatia]

If you're using o365 you can use the FILTER function and spilled range reference.

In C1 type =FILTER(EleNo,IF(A1="N/A",EleNo="N/A",EleNo<>"N/A"))
In A2 the data validation criteria will be =C1#

 

[drewberts]

Whilst this doesn't automatically put N/A in A2, how about

+Fluff New.xlsm
	A	B	C	D	E
1	N/A		N/A	1
2	N/A		a	2
3			b	3
4				4
5
6
Main

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=EleType
A2	List	=IF(A1="N/A",C1,EleNo)

Many thanks @Fluff. Whilst it doesn't quite deal with the auto N/A it does work for me

 

[drewberts]

If you're using o365 you can use the FILTER function and spilled range reference.

In C1 type =FILTER(EleNo,IF(A1="N/A",EleNo="N/A",EleNo<>"N/A"))
In A2 the data validation criteria will be =C1#

Many thanks. I've looked at this and I get a ~CALC error in my list and a #SPILL error in my cell

 

[Fluff]

Glad we could help & thanks for the feedback.