Data Validation List Dependent on Another List

T

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
137
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Activity IDUnique Action ID
2ACT000001ACT000001B
3ACT000002ACT000002A
4ACT000003ACT000003A
5ACT000001ACT000001A
6ACT000049ACT000049A
7ACT000021ACT000021A
8ACT000021ACT000021B
9ACT000021ACT000021C
10ACT000021ACT000021D
11ACT000021ACT000021E
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B7:B11B7=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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi

is this the logic you are looking at


Book2
ABCDEFGHIJ
1Activity IDUnique Action IDUnique List of Activity IDDependent List of Drop Down
2ACT000001ACT000001BACT000001ACT000001BACT000001ACT000003A
3ACT000002ACT000002AACT000002ACT000001A
4ACT000003ACT000003AACT000003
5ACT000001ACT000001AACT000049
6ACT000049ACT000049AACT000021
7ACT000021ACT000021A
8ACT000021ACT000021B
9ACT000021ACT000021C
10ACT000021ACT000021D
11ACT000021ACT000021E
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=UNIQUE(A2:A11,0,0)
G2:G3G2=UNIQUE(FILTER(B2:B11,A2:A11=I2),0,0)
B2:B6B2=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B7:B11B7=A7&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$6:A7,A7),4),1,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I2List=$E$2:$E$22
J2List=$G$2:$G$24


1635842495340.png
 
Upvote 0
Tashat said:
I have another sheet where the user can select the Activity Number in a data validation dropdown list
Click to expand...
Is that a choice in a single cell only or is there a range of cells (column?) where that first choice can be made?
 
Upvote 0
ravi2628 said:
Hi

is this the logic you are looking at


Book2
ABCDEFGHIJ
1Activity IDUnique Action IDUnique List of Activity IDDependent List of Drop Down
2ACT000001ACT000001BACT000001ACT000001BACT000001ACT000003A
3ACT000002ACT000002AACT000002ACT000001A
4ACT000003ACT000003AACT000003
5ACT000001ACT000001AACT000049
6ACT000049ACT000049AACT000021
7ACT000021ACT000021A
8ACT000021ACT000021B
9ACT000021ACT000021C
10ACT000021ACT000021D
11ACT000021ACT000021E
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=UNIQUE(A2:A11,0,0)
G2:G3G2=UNIQUE(FILTER(B2:B11,A2:A11=I2),0,0)
B2:B6B2=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B7:B11B7=A7&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$6:A7,A7),4),1,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I2List=$E$2:$E$22
J2List=$G$2:$G$24


View attachment 50296
Click to expand...
Hi Ravi2628

Thank you so much for taking the time to help me with this. This solution seems really close to working for me. The only issue is that if the user selects ACT000021 then I want the dependent drop-down list to offer ACT000021A, ACT000021B, ACT000021C, ACT000021D. So the drop-down list only offers the relevant Action IDs depending on which Activity ID they select. it's very hard for me to explain. At the moment (unless I've done it wrong), it doesn't matter which Activity ID the user selects, the dependent list only offers ACT000001A and ACT00000B. Is there a way of making the dependent list change? Thank you again for your help.
 
Upvote 0
Peter_SSs said:
Is that a choice in a single cell only or is there a range of cells (column?) where that first choice can be made?
Click to expand...
Hi Peter_sSs it's a range of cells in a column where the first choice can be made. So there are multiple rows (records) where the user can select the Activity ID, then I want the adjacent column to have a drop-down list with only the relevant Action IDs. The user can then repeat this action on the next row down for a new record and which may or may not have the same Activity ID as above, but then they would select a different Action ID from the drop-down list.

Sorry - it's very hard to explain.
 
Upvote 0
Tashat said:
Hi Ravi2628

Thank you so much for taking the time to help me with this. This solution seems really close to working for me. The only issue is that if the user selects ACT000021 then I want the dependent drop-down list to offer ACT000021A, ACT000021B, ACT000021C, ACT000021D. So the drop-down list only offers the relevant Action IDs depending on which Activity ID they select. it's very hard for me to explain. At the moment (unless I've done it wrong), it doesn't matter which Activity ID the user selects, the dependent list only offers ACT000001A and ACT00000B. Is there a way of making the dependent list change? Thank you again for your help.
Click to expand...
Share a sample example of your data how it is required so that we can find the logic and share it with you
 
Upvote 0
Tashat said:
it's a range of cells in a column where the first choice can be made.
Click to expand...
Then I would set up the second sheet something like this. The exact layout is up to you and Columns E, F, G, ... etc in my sheet could be hidden

Tashat.xlsm
ABCDEFGHIJKL
1Choice 1Choice 2ACT000001
2ACT000001ACT000001AACT000002ACT000001BACT000001A
3ACT000003 
4ACT000049ACT000049ACT000049A
5ACT000021ACT000021ACT000021AACT000021BACT000021CACT000021DACT000021E
6 
7 
8 
9 
10 
Sheet2
Cell Formulas
RangeFormula
E1:E5E1=UNIQUE(Sheet1!A2:A11)
G2:H2,G5:K5,G3:G4,G6:G10G2=TRANSPOSE(FILTER(Sheet1!B$2:B$11,Sheet1!A$2:A$11=A2,""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A2:A10List=$E$1#
B2:B10List=G2#


Example of second choice:

1635845731981.png
 
Upvote 0
Peter_SSs said:
Then I would set up the second sheet something like this. The exact layout is up to you and Columns E, F, G, ... etc in my sheet could be hidden

Tashat.xlsm
ABCDEFGHIJKL
1Choice 1Choice 2ACT000001
2ACT000001ACT000001AACT000002ACT000001BACT000001A
3ACT000003 
4ACT000049ACT000049ACT000049A
5ACT000021ACT000021ACT000021AACT000021BACT000021CACT000021DACT000021E
6 
7 
8 
9 
10 
Sheet2
Cell Formulas
RangeFormula
E1:E5E1=UNIQUE(Sheet1!A2:A11)
G2:H2,G5:K5,G3:G4,G6:G10G2=TRANSPOSE(FILTER(Sheet1!B$2:B$11,Sheet1!A$2:A$11=A2,""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A2:A10List=$E$1#
B2:B10List=G2#


Example of second choice:

View attachment 50299
Click to expand...
Hi - thank you so much for taking the time with this. I think I'm getting closer to the solution I need. However, this is what I've managed to do, which I can see doesn't quite work with my list, because my list has repeating Activity IDs in column A, which skews it. I can see mine isn't quite the same as yours because your choice 1 has stripped out the repeating Action IDs, so perhaps I've done it wrong, or misunderstood the cell reference?

Book1
ABCDEFGHIJK
1Associated Activity IDUnique Action IDACT000001
2ACT000001ACT000001BACT000002ACT000001BACT000001A
3ACT000002ACT000002AACT000003ACT000002A
4ACT000003ACT000003AACT000049ACT000003A
5ACT000001ACT000001AACT000021ACT000001A
6ACT000049ACT000049AACT000049A
7ACT000021ACT000021A
8ACT000021ACT000021B
9ACT000021ACT000021C
10ACT000021ACT000021D
11ACT000021ACT000021E
12ACT000021ACT000021F
13
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=UNIQUE(A2:A12)
G2:H2,G3:G6G2=TRANSPOSE(FILTER(B2:B12,A2:A12=A2,""))
B2:B5B2=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B6:B12B6=A6&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$6:A6,A6),4),1,"")
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Then I would set up the second sheet something like this. The exact layout is up to you and Columns E, F, G, ... etc in my sheet could be hidden

Tashat.xlsm
ABCDEFGHIJKL
1Choice 1Choice 2ACT000001
2ACT000001ACT000001AACT000002ACT000001BACT000001A
3ACT000003 
4ACT000049ACT000049ACT000049A
5ACT000021ACT000021ACT000021AACT000021BACT000021CACT000021DACT000021E
6 
7 
8 
9 
10 
Sheet2
Cell Formulas
RangeFormula
E1:E5E1=UNIQUE(Sheet1!A2:A11)
G2:H2,G5:K5,G3:G4,G6:G10G2=TRANSPOSE(FILTER(Sheet1!B$2:B$11,Sheet1!A$2:A$11=A2,""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A2:A10List=$E$1#
B2:B10List=G2#


Example of second choice:

View attachment 50299
Click to expand...
I think I've made some progress and it is now working getting the Unique Action IDs to display horizontally across the page - as per below. However, now I'm having trouble getting the Data Validation List to work so that Choice 2 is dependent on Choice 1. Choice 1 works fine, but Choice 2 doesn't seem to be dependent on Choice 1. Thanks everyone for the help so far.

Book1
ABCDEFGHIJKL
1Associated Activity IDUnique Action IDACT000001ACT000001BACT000001A
2ACT000001ACT000001BACT000002ACT000002A
3ACT000002ACT000002AACT000003ACT000003A
4ACT000003ACT000003AACT000049ACT000049A
5ACT000001ACT000001AACT000021ACT000021AACT000021BACT000021CACT000021DACT000021EACT000021F
6ACT000049ACT000049A 
7ACT000021ACT000021A 
8ACT000021ACT000021B 
9ACT000021ACT000021C 
10ACT000021ACT000021D
11ACT000021ACT000021E
12ACT000021ACT000021F
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=UNIQUE(A2:A12)
G1:H1,G5:L5,G2:G4,G6:G9G1=IF(E1=0,"",TRANSPOSE(FILTER(B2:B12,A2:A12=E1,"")))
B2:B5B2=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A2:A$6,A2),4),1,"")
B6:B12B6=A6&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$6:A6,A6),4),1,"")
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJK
1Activity IDUnique Action IDUnique IDActions
2ACT000001ACT000001BACT000001ACT000001BACT000001A
3ACT000002ACT000002AACT000002ACT000002A
4ACT000003ACT000003AACT000003ACT000003A
5ACT000001ACT000001AACT000049ACT000049A
6ACT000049ACT000049AACT000021ACT000021AACT000021BACT000021CACT000021DACT000021E
7ACT000021ACT000021A 
8ACT000021ACT000021B 
9ACT000021ACT000021C 
10ACT000021ACT000021D 
11ACT000021ACT000021E 
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=UNIQUE(A2:A11)
F2:G2,F6:J6,F3:F5,F7:F11F2=TRANSPOSE(FILTER(B$2:B$11,A$2:A$11=E2,""))
Dynamic array formulas.


Data Validation as shown below.

Tashat.xlsm
AB
1Choice 1Choice 2
2ACT000001ACT000001A
3
4ACT000049
5ACT000021
6
7
8
9
10
Sheet2
Cells with Data Validation
CellAllowCriteria
A2:A10List=Sheet1!$E$2#
B2:B10List=INDEX(Sheet1!$F:$F,MATCH(A2,Sheet1!$E:$E,0))#
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

D
  • Question Question
Can a Data Validation List be auto filtered by reference to a cell content?
Replies
1
Views
113
Joe4
Joe4
R
  • Question Question
VBA Loop Through Data Validation Drop Down List
Replies
5
Views
2K
rlv01
R
S
  • Solved
Data List Change + PDF Export on button click
Replies
4
Views
558
StevenE06
S
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
505
zero269
Z
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
108
Lux Aeterna
L

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top