Drop down box help needed

Ron Abba

New Member
Joined
Jun 25, 2020
Messages
6
Office Version
  1. 2016
Good day all,
I have a question about using a drop down box that will list items based on another drop down box cells input. On sheet1B2 I have a Drop down box that is used to select trucks in different departments, such as Sani, admin, ect. On sheet4 I have a bunch of services listed by each Department that have to be performed. I can create the drop down box on sheet1B2 that shows the department, but I can not get it to list the services for the chosen Department in sheet1C11 drop down box. I have to use the Drop down box in sheet1C11 to select the type of service that is due based on the department chosen. Thanks for reading. :)
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
Hi Ron Abba,

If you have a smaller list of Departments you could use Named Tables and INDIRECT but you don't supply much information so I'll use a method to build the selection lists and OFFSET to provide the dropdowns.

Sheet4 columns A and B have your Departments and the Services performed by each.

Sheet4 column E builds a list of distinct Departments for that dropdown in Sheet1 B2. The Sheet1 B2 Data Validation list is
=OFFSET(Sheet4!$E$2,,,COUNTIF(Sheet4!$E$2:$E$20,"> "))
so it offers the Sheet4 distinct Depts up to the first blank entry.

Sheet4 column G builds the list of Services based on the Sheet1 B2 Dept selection using
=IFERROR(INDEX($B$2:$B$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999=Sheet1!$B$2),ROW()-ROW($G$1))),"")

Sheet1 C11 Data Validation again uses OFFSET to offer the Sheet4 column G entries up to the first blank entry.
=OFFSET(Sheet4!$G$2,,,COUNTIF(Sheet4!$G$2:$G$20,"> "))



RonAbba.xlsx
ABCDEFG
1DepartmentServiceDept DropdownItem Dropdown
2SaniCollectSaniBoxes
3SaniCleanAdminPallets
4SaniWashTransportLoose Items
5SaniDeliver People
6SaniPark Empty
7SaniDrive Liquids
8AdminMail  
9AdminCargo  
10AdminStaff  
11TransportBoxes  
12TransportPallets  
13TransportLoose Items  
14TransportPeople  
15TransportEmpty  
16TransportLiquids  
Sheet4
Cell Formulas
RangeFormula
E2:E16E2=INDEX($A$2:$A$999,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$999),),0))&""
G2:G16G2=IFERROR(INDEX($B$2:$B$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999=Sheet1!$B$2),ROW()-ROW($G$1))),"")


RonAbba.xlsx
BC
1Dept Selection
2Transport
3
4
5
6
7
8
9
10Service Selection
11Loose Items
Sheet1
Cells with Data Validation
CellAllowCriteria
B2List=OFFSET(Sheet4!$E$2,,,COUNTIF(Sheet4!$E$2:$E$20,"> "))
C11List=OFFSET(Sheet4!$G$2,,,COUNTIF(Sheet4!$G$2:$G$20,"> "))
 

Ron Abba

New Member
Joined
Jun 25, 2020
Messages
6
Office Version
  1. 2016
1593206630869.png

Thanks you so much for your help TS, I will try to post more info to clear things up..
Sheet 4 is where I have all my tables.

1593207063336.png

Sheet1 B2 - cell in yellow - is where I select the dept -which is out lines on sheet4 A1:F
Sheet1 C10, C11, &C12- Cells in Green - will all be drop downs to select the services needed after the Dept has been chosen.

Hope this adds more light on what Im hoping to do. Thanks so much for your time with this.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
Ron,

Here's the modified sheets:

RonAbba2.xlsx
ABCDEFGHI
1FenceSaniStorageForkliftsTrailersAdminServices FenceServDrop
2PM "A" Words here for Services FencePM "A" Words here for Services Trailers
3PM "B" Words here for Services FencePM "B" Words here for Services Trailers
4PM "C" Words here for Services FencePM "C" Words here for Services Trailers
5 
6 
7 
8Services Sani
9PM "A" Words here for Services Sani
10PM "B" Words here for Services Sani
11PM "C" Words here for Services Sani
12
13
14
15Services Admin
16PM "A" Words here for Services Admin
17PM "B" Words here for Services Admin
18PM "C" Words here for Services Admin
19
20
21
22Services Forklifts
23PM "A" Words here for Services Forklifts
24PM "B" Words here for Services Forklifts
25PM "C" Words here for Services Forklifts
26
27
28
29Services Storage
30PM "A" Words here for Services Storage
31PM "B" Words here for Services Storage
32PM "C" Words here for Services Storage
33
34
35
36Services Trailers
37PM "A" Words here for Services Trailers
38PM "B" Words here for Services Trailers
39PM "C" Words here for Services Trailers
Sheet4
Cell Formulas
RangeFormula
I2:I7I2=INDEX($G$1:$G$42,MATCH("Services "&Sheet1!$B$2,$G$1:$G$42,0)+ROW(I1))&""


RonAbba2.xlsx
BC
1Dept Selection
2Trailers
3
4
5
6
7
8
9
10PM "A" Words here for Services Trailers
11PM "B" Words here for Services Trailers
12PM "C" Words here for Services Trailers
13
14
15
Sheet1
Cells with Data Validation
CellAllowCriteria
B2List=Sheet4!$A$1:$F$1
C10:C15List=OFFSET(Sheet4!$I$2,,,COUNTIF(Sheet4!$I$2:$I$7,"> "))
 

Ron Abba

New Member
Joined
Jun 25, 2020
Messages
6
Office Version
  1. 2016
ToadStool, Thank you so much! I cant tell you how long I was wrestling with this, many frustrated nights.

Once again thank you for your time and knowledge, your a true resource to this community.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,886
Messages
5,574,833
Members
412,620
Latest member
sharma7s
Top