3 dependent drop downs

P

pyclen

Board Regular
Joined
Jan 17, 2022
Messages
91
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there,

I do have an issue creating dependent drop downs. I need a total of 3 dropdowns.
The 1st is the Offering (NCE, Generic, Commercial) where there are 3 choices, then based on the selection of the Offering a number of technologies are shown in the 2nd dropdown, and lastly based on that 2nd dropdown a rate is displayed in the 3rd dropdown.
I can clobber something together w/2 dropdowns but then it leaves me behind. Any help is appreciated.
The table in columns H & I can also be horizontal over 6 columns.

Example: if you choose NCE, Technology 1 the rate is 200

any help is greatly appreciated

Book1
BCDEFGHIJKL
1
2Offering drop downTechnology dropdown Rate dropdown NCErateNCE
3Technology 1200Generic
4Technology 2240Commercial
5Technology 3278
6Technology 4200
7Technology 5325
8Technology 6150
9
10Genericrate
11Tech 1300
12Tech 2222
13Tech 3245
14Tech 4216
15Tech 5357
16Tech 6234
17
18Commercialrate
19something 1123
20something 2125
21something 3130
22something 4135
23something 5140
24something 6147
Sheet1
Cell Formulas
RangeFormula
L2L2=H2
L3L3=H10
L4L4=H18
Cells with Data Validation
CellAllowCriteria
B3:B5List=offering
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Actually, there is another twist, I also need to have the answer for the rate in Euro, so essentially a fourth row, the cost in Euro is for clients in Europe whereas the rate in USD is for US/CAN and elsewhere
I added the Euro rates in a new column

drops.xlsx
BCDEFGHIJKLMN
2Offering drop downTechnology dropdown Rate dropdown NCErate [USD]rate (Euro)Offering drop downTechnology dropdown Rate dropdown
3Technology 1200175
4Technology 2240200
5Technology 3278250
6Technology 4200175
7Technology 5325295
8Technology 6150125
9
10Genericrate
11Tech 1300275
12Tech 2222200
13Tech 3245230
14Tech 4216198
15Tech 5357325
16Tech 6234215
17
18Commercialrate
19something 1123110
20something 2125115
21something 3130120
22something 4135123
23something 5140127
24something 6147130
Sheet1
Cells with Data Validation
CellAllowCriteria
B3:B5List=offering
L3:L5List=offering
 
Upvote 0
Book3
ABCDEFGHIJKLMNOPQRSTUVW
1
2Offering drop downTechnology dropdown Rate (USD)Rate (Euro)NCEraterateLookupOfferingTechnologyRate (USD)Rate (Euro)OfferingTechnologyRate (USD)Rate (Euro)
3Commercialsomething 2125115Technology 1200rateNCETechnology 1200NCETechnology 1200175Commercialsomething 1125
4Technology 2240rateNCETechnology 2240NCETechnology 2240200Genericsomething 2
5Technology 3278NCETechnology 3278NCETechnology 3278250NCEsomething 3
6Technology 4200NCETechnology 4200NCETechnology 4200175something 4
7Technology 5325NCETechnology 5325NCETechnology 5325295something 5
8Technology 6150NCETechnology 6150NCETechnology 6150125something 6
9GenericTech 1300GenericTech 1300275
10GenericrateGenericTech 2222GenericTech 2222200
11Tech 1300GenericTech 3245GenericTech 3245230
12Tech 2222GenericTech 4216GenericTech 4216198
13Tech 3245GenericTech 5357GenericTech 5357325
14Tech 4216GenericTech 6234GenericTech 6234215
15Tech 5357Commercialsomething 1123Commercialsomething 1123110
16Tech 6234Commercialsomething 2125Commercialsomething 2125115
17Commercialsomething 3130Commercialsomething 3130120
18CommercialrateCommercialsomething 4135Commercialsomething 4135123
19something 1123Commercialsomething 5140Commercialsomething 5140127
20something 2125Commercialsomething 6147Commercialsomething 6147130
21something 3130
22something 4135
23something 5140
24something 6147
Sheet1
Cell Formulas
RangeFormula
D3D3=VLOOKUP(A3&B3&C3,M3:Q20,5,0)
T3:T5T3=SORT(UNIQUE(FILTER(N3:N20,N3:N20<>"")))
U3:U8,V3U3=SORT(UNIQUE(FILTER(O3:O20,(O3:O20<>"")*(N3:N20=A3))))
K2K2=H2
K3K3=H10
K4K4=H18
M3:M20M3=N3&O3&P3
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$T$3:$T$5
B3List=$U$3:$U$22
C3List=$V$3:$V$21
 
Upvote 0
Armstrong_N14 said:
Book3
ABCDEFGHIJKLMNOPQRSTUVW
1
2Offering drop downTechnology dropdown Rate (USD)Rate (Euro)NCEraterateLookupOfferingTechnologyRate (USD)Rate (Euro)OfferingTechnologyRate (USD)Rate (Euro)
3Commercialsomething 2125115Technology 1200rateNCETechnology 1200NCETechnology 1200175Commercialsomething 1125
4Technology 2240rateNCETechnology 2240NCETechnology 2240200Genericsomething 2
5Technology 3278NCETechnology 3278NCETechnology 3278250NCEsomething 3
6Technology 4200NCETechnology 4200NCETechnology 4200175something 4
7Technology 5325NCETechnology 5325NCETechnology 5325295something 5
8Technology 6150NCETechnology 6150NCETechnology 6150125something 6
9GenericTech 1300GenericTech 1300275
10GenericrateGenericTech 2222GenericTech 2222200
11Tech 1300GenericTech 3245GenericTech 3245230
12Tech 2222GenericTech 4216GenericTech 4216198
13Tech 3245GenericTech 5357GenericTech 5357325
14Tech 4216GenericTech 6234GenericTech 6234215
15Tech 5357Commercialsomething 1123Commercialsomething 1123110
16Tech 6234Commercialsomething 2125Commercialsomething 2125115
17Commercialsomething 3130Commercialsomething 3130120
18CommercialrateCommercialsomething 4135Commercialsomething 4135123
19something 1123Commercialsomething 5140Commercialsomething 5140127
20something 2125Commercialsomething 6147Commercialsomething 6147130
21something 3130
22something 4135
23something 5140
24something 6147
Sheet1
Cell Formulas
RangeFormula
D3D3=VLOOKUP(A3&B3&C3,M3:Q20,5,0)
T3:T5T3=SORT(UNIQUE(FILTER(N3:N20,N3:N20<>"")))
U3:U8,V3U3=SORT(UNIQUE(FILTER(O3:O20,(O3:O20<>"")*(N3:N20=A3))))
K2K2=H2
K3K3=H10
K4K4=H18
M3:M20M3=N3&O3&P3
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$T$3:$T$5
B3List=$U$3:$U$22
C3List=$V$3:$V$21
Click to expand...
Hi Armstrong

I appreciate the reply and the effort however I am not able to get this to work beyond the first row in Column A B C D (row 3)

not sure what is wrong but the selections in Row 4 and down just do not change in Column B C D when I change Column A, i.e., no other value beyond what I have in the first row is visible/available in B & C and therefore D is not calculating. What am I missing?

New Cost Sheet.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2Offering drop-downTechnology drop-downRate (USD)Rate (Euro)NCEraterateLookupofferingTechnologyRate (USD)Rate (Euro)OfferingTechnologyRate (USD)Rate (Euro)
3NCETechnology 2240200Technology 1200rateNCETechnology 1200NCETechnology 1200175CommercialTechnology 1240240
4GenericTechnology 1200#N/ATechnology 2240rateNCETechnology 2240NCETechnology 2240200GenericTechnology 2
5CommercialTechnology 1200#N/ATechnology 3278NCETechnology 3278NCETechnology 3278250NCETechnology 3
6NCETechnology 1200#N/ATechnology 4200NCETechnology 4200NCETechnology 4200175Technology 4
7NCETechnology 1200#N/ATechnology 5325NCETechnology 5325NCETechnology 5325295Technology 5
8NCETechnology 1200#N/ATechnology 6100NCETechnology 6100NCETechnology 6100125Technology 6
9NCETechnology 1200#N/AGenericTech 1300GenericTech 1300275
10NCETechnology 1200#N/AGenericrateGenericTech 2222GenericTech 2222200
11Tech 1300GenericTech 3245GenericTech 3245230
12Tech 2222GenericTech 4216GenericTech 4216198
13Tech 3245GenericTech 5357GenericTech 5357325
14Tech 4216GenericTech 6234GenericTech 6234215
15Tech 5357Commercialsomething 1123Commercialsomething 1123110
16Tech 6234Commercialsomething 2125Commercialsomething 2125115
17Commercialsomething 3130Commercialsomething 3130120
18CommercialrateCommercialsomething 4135Commercialsomething 4135123
19something 1123Commercialsomething 5140Commercialsomething 5140127
20something 2125Commercialsomething 6145Commercialsomething 6145130
21something 3130 
22something 4135 
23something 5140 
24something 6145 
25
26
27
drops
Cell Formulas
RangeFormula
P2:Q2P2=C2
T3:T5T3=SORT(UNIQUE(FILTER(N3:N20,N3:N20<>"")))
U3:U8,V3U3=SORT(UNIQUE(FILTER(O3:O20,(O3:O20<>"")*(N3:N20=A3))))
W3W3=SORT(UNIQUE(FILTER(P3:P20,(P3:P20<>"")*(O3:O20=B3))))
K2K2=H2
K3K3=H10
K4K4=H18
D3:D10D3=VLOOKUP(A3&B3&C3,M3:Q20,5,0)
M3:M24M3=N3&O3&P3
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3:A10List=$T$3:$T$5
B3:B10List=$U$3:$U$22
C3:C10List=$V$3:$V$21
 
Upvote 0
I realized that it would just work for one-time selection out of the drop-down..
Now Im beat. sorry..
 
Upvote 0
U3:U8,V3U3=SORT(UNIQUE(FILTER(O3:O20,(O3:O20<>"")*(N3:N20=A3))))
W3W3=SORT(UNIQUE(FILTER(P3:P20,(P3:P20<>"")*(O3:O20=B3))))
thats where the problem is. A3 and B3 Needs to be Dynamic (to be A4, A5,A6 / B3, B4,B5,B6 and so on)
 
Upvote 0
I may have found a fix but theres a limitation. Hope this works

Book3
ABCDEFGHIJKLMN
1Offering drop downTechnology dropdown Rate (USD)Rate (Euro)SelectorLookup 1Lookup 2OfferingTechnologyRate (USD)Rate (Euro)OfferingTechnology
2GenericTech 22222003NCETechnology 1200NCETechnology 1NCETechnology 1200175CommercialTech 1
3GenericTech 3245230NCETechnology 2240NCETechnology 2NCETechnology 2240200GenericTech 2
4  NCETechnology 3278NCETechnology 3NCETechnology 3278250NCETech 3
5  NCETechnology 4200NCETechnology 4NCETechnology 4200175Tech 4
6  NCETechnology 5325NCETechnology 5NCETechnology 5325295Tech 5
7NCETechnology 6150NCETechnology 6NCETechnology 6150125Tech 6
8GenericTech 1300GenericTech 1GenericTech 1300275
9GenericTech 2222GenericTech 2GenericTech 2222200
10GenericTech 3245GenericTech 3GenericTech 3245230
11GenericTech 4216GenericTech 4GenericTech 4216198
12GenericTech 5357GenericTech 5GenericTech 5357325
13GenericTech 6234GenericTech 6GenericTech 6234215
14Commercialsomething 1123Commercialsomething 1Commercialsomething 1123110
15Commercialsomething 2125Commercialsomething 2Commercialsomething 2125115
16Commercialsomething 3130Commercialsomething 3Commercialsomething 3130120
17Commercialsomething 4135Commercialsomething 4Commercialsomething 4135123
18Commercialsomething 5140Commercialsomething 5Commercialsomething 5140127
19Commercialsomething 6147Commercialsomething 6Commercialsomething 6147130
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(VLOOKUP(A2&B2,$H$2:$L$19,4,0),"")
D2:D6D2=IFERROR(VLOOKUP(A2&B2&C2,$G$2:$L$19,6,0),"")
F2F2=MAX(($A:$A<>"")*(ROW($A:$A)))
G2:G19G2=I2&J2&K2
H2:H19H2=I2&J2
M2:M4M2=SORT(UNIQUE(FILTER(I2:I19,I2:I19<>"")))
N2:N7N2=SORT(UNIQUE(FILTER(J2:J19,(J2:J19<>"")*(I2:I19=INDIRECT(CONCATENATE("A"&$F$2))))))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A2:A6List=$M$2:$M$4
B2:B6List=$N$2:$N$7
 
Upvote 0
You can delete the formula on F2 and create a module on the sheet then past this code:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
WS.Range("F2") = ActiveCell.Row

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Excel: drop down list colouring based on "dependent" list
Replies
5
Views
158
Peter_SSs
Peter_SSs
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M
J
  • Question Question
Dependent dropdown with offset formula
Replies
5
Views
508
juliecooper255
J
C
  • Question Question
Custom Data Validation drop downs dependent on a cell value
Replies
5
Views
805
myall_blues
myall_blues
R
  • Question Question
Dependent Drop Downs
Replies
5
Views
804
kevin9999
K

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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