Data Validation List Dependent on Another List

[Tashat]

Hi All

I have a list of Activity IDs and linked Action IDs as per below. The Action ID is calculated based on the Activity ID

Book1
	A	B
1	Activity ID	Unique Action ID
2	ACT000001	ACT000001B
3	ACT000002	ACT000002A
4	ACT000003	ACT000003A
5	ACT000001	ACT000001A
6	ACT000049	ACT000049A
7	ACT000021	ACT000021A
8	ACT000021	ACT000021B
9	ACT000021	ACT000021C
10	ACT000021	ACT000021D
11	ACT000021	ACT000021E
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B7:B11	B7	=A7&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$6:A7,A7),4),1,"")

Based on the IDs above, I have another sheet where the user can select the Activity Number in a data validation dropdown list - e.g. ACT000001 and then can select the linked Action Number in the next column. Is there a way of shrinking the drop-down list in the action number column to only present the user with the possible Action Numbers linked with that Activity ID, so the data validation list in the second column is dependent on what the user enters in the first column? e.g.

If the user selects ACT00001 from the drop down list, then the next dropdown list only gives them the choice of ACT000001a or ACT000001b

 

[Tashat]

OK, I think you were on the right track for a simpler version than I proposed but you don't need the =IF(E1=0 part.
try this set-up

Formula in F1 copied down as far as you might ever need. In theory that would be to the bottom of the the column A:B data in case all column A values were unique.

Tashat.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Activity ID	Unique Action ID			Unique ID	Actions
2	ACT000001	ACT000001B			ACT000001	ACT000001B	ACT000001A
3	ACT000002	ACT000002A			ACT000002	ACT000002A
4	ACT000003	ACT000003A			ACT000003	ACT000003A
5	ACT000001	ACT000001A			ACT000049	ACT000049A
6	ACT000049	ACT000049A			ACT000021	ACT000021A	ACT000021B	ACT000021C	ACT000021D	ACT000021E
7	ACT000021	ACT000021A
8	ACT000021	ACT000021B
9	ACT000021	ACT000021C
10	ACT000021	ACT000021D
11	ACT000021	ACT000021E
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=UNIQUE(A2:A11)
F2:G2,F6:J6,F3:F5,F7:F11	F2	=TRANSPOSE(FILTER(B$2:B$11,A$2:A$11=E2,""))
Dynamic array formulas.

Data Validation as shown below.

Tashat.xlsm
	A	B
1	Choice 1	Choice 2
2	ACT000001	ACT000001A
3
4	ACT000049
5	ACT000021
6
7
8
9
10
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=Sheet1!$E$2#
B2:B10	List	=INDEX(Sheet1!$F:$F,MATCH(A2,Sheet1!$E:$E,0))#

That's fantastic! Thank you so much for all your help with this

 

[Peter_SSs]

You're welcome. Glad we got there in the end.