4 dependent drop downs

D

DazCD

New Member
Joined
Aug 19, 2024
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Good afternoon & Happy new year.

I'm having trouble creating (technically) 3 drop downs.

The first drop down would be my starch (This one technically doesn't need to be dependent) , 2nd drop down sucrose, 3rd Milk fat, 4th Milk proteins to then get the meursing code as per Col E.

I currently have this set up with unique values from all 4 columns but this could lead to mistakes.

For example, if the first drop down is "0 - 4.99", the second drop down would show all values to the right of the first value and then so on.

i'm not sure if i'm explaining it well enough, thank you in advance.




Meursing Codes - table layout.xlsx
ABCDE
20 - 4.990 - 4.990 - 1.490 - 2.497000
30 - 4.995 - 29.990 - 1.490 - 2.497001
40 - 4.9930 - 49.990 - 1.490 - 2.497002
50 - 4.9950 - 69.990 - 1.490 - 2.497003
60 - 4.99≥700 - 1.490 - 2.497004
75 - 24.990 - 4.990 - 1.490 - 2.497005
85 - 24.995 - 29.990 - 1.490 - 2.497006
95 - 24.9930 - 49.990 - 1.490 - 2.497007
105 - 24.9950 - 69.990 - 1.490 - 2.497008
115 - 24.99≥700 - 1.490 - 2.497009
1225 - 49.990 - 4.990 - 1.490 - 2.497010
1325 - 49.995 - 29.990 - 1.490 - 2.497011
1425 - 49.9930 - 49.990 - 1.490 - 2.497012
1525 - 49.99≥500 - 1.490 - 2.497013
1650 - 74.990 - 4.990 - 1.490 - 2.497015
1750 - 74.995 - 29.990 - 1.490 - 2.497016
1850 - 74.99≥300 - 1.490 - 2.497017
19>750 - 4.990 - 1.490 - 2.497758
20>75≥50 - 1.490 - 2.497759
210 - 4.990 - 4.990 - 1.492.5 - 5.997020
220 - 4.995 - 29.990 - 1.492.5 - 5.997021
230 - 4.9930 - 49.990 - 1.492.5 - 5.997022
240 - 4.9950 - 69.990 - 1.492.5 - 5.997023
250 - 4.99≥700 - 1.492.5 - 5.997024
265 - 24.990 - 4.990 - 1.492.5 - 5.997025
275 - 24.995 - 29.990 - 1.492.5 - 5.997026
285 - 24.9930 - 49.990 - 1.492.5 - 5.997027
295 - 24.9950 - 69.990 - 1.492.5 - 5.997028
305 - 24.99≥700 - 1.492.5 - 5.997029
3125 - 49.990 - 4.990 - 1.492.5 - 5.997030
3225 - 49.995 - 29.990 - 1.492.5 - 5.997031
3325 - 49.9930 - 49.990 - 1.492.5 - 5.997032
3425 - 49.99≥500 - 1.492.5 - 5.997033
3550 - 74.990 - 4.990 - 1.492.5 - 5.997035
3650 - 74.995 - 29.990 - 1.492.5 - 5.997036
3750 - 74.99≥300 - 1.492.5 - 5.997037
38>750 - 4.990 - 1.492.5 - 5.997768
39>75≥50 - 1.492.5 - 5.997769
400 - 4.990 - 4.990 - 1.496 - 17.997040
410 - 4.995 - 29.990 - 1.496 - 17.997041
420 - 4.9930 - 49.990 - 1.496 - 17.997042
430 - 4.9950 - 69.990 - 1.496 - 17.997043
440 - 4.99≥700 - 1.496 - 17.997044
455 - 24.990 - 4.990 - 1.496 - 17.997045
465 - 24.995 - 29.990 - 1.496 - 17.997046
475 - 24.9930 - 49.990 - 1.496 - 17.997047
485 - 24.9950 - 69.990 - 1.496 - 17.997048
495 - 24.99≥700 - 1.496 - 17.997049
5025 - 49.990 - 4.990 - 1.496 - 17.997050
5125 - 49.995 - 29.990 - 1.496 - 17.997051
5225 - 49.9930 - 49.990 - 1.496 - 17.997052
5325 - 49.99≥500 - 1.496 - 17.997053
5450 - 74.990 - 4.990 - 1.496 - 17.997055
5550 - 74.995 - 29.990 - 1.496 - 17.997056
5650 - 74.99≥300 - 1.496 - 17.997057
57>750 - 4.990 - 1.496 - 17.997778
58>75≥50 - 1.496 - 17.997779
590 - 4.990 - 4.990 - 1.4918 - 29.997060
600 - 4.995 - 29.990 - 1.4918 - 29.997061
610 - 4.9930 - 49.990 - 1.4918 - 29.997062
620 - 4.9950 - 69.990 - 1.4918 - 29.997063
630 - 4.99≥700 - 1.4918 - 29.997064
645 - 24.990 - 4.990 - 1.4918 - 29.997065
655 - 24.995 - 29.990 - 1.4918 - 29.997066
665 - 24.9930 - 49.990 - 1.4918 - 29.997067
675 - 24.9950 - 69.990 - 1.4918 - 29.997068
685 - 24.99≥700 - 1.4918 - 29.997069
6925 - 49.990 - 4.990 - 1.4918 - 29.997070
7025 - 49.995 - 29.990 - 1.4918 - 29.997071
7125 - 49.9930 - 49.990 - 1.4918 - 29.997072
7225 - 49.99≥500 - 1.4918 - 29.997073
7350 - 74.990 - 4.990 - 1.4918 - 29.997075
7450 - 74.995 - 29.990 - 1.4918 - 29.997076
7550 - 74.99≥300 - 1.4918 - 29.997077
76>750 - 4.990 - 1.4918 - 29.997788
77>75≥50 - 1.4918 - 29.997789
780 - 4.990 - 4.990 - 1.4930 - 59.997080
790 - 4.995 - 29.990 - 1.4930 - 59.997081
800 - 4.9930 - 49.990 - 1.4930 - 59.997082
810 - 4.9950 - 69.990 - 1.4930 - 59.997083
820 - 4.99≥700 - 1.4930 - 59.997084
835 - 24.990 - 4.990 - 1.4930 - 59.997085
845 - 24.995 - 29.990 - 1.4930 - 59.997086
855 - 24.9930 - 49.990 - 1.4930 - 59.997087
865 - 24.9950 - 69.990 - 1.4930 - 59.997088
875 - 24.99≥700 - 1.4930 - 59.99
8825 - 49.990 - 4.990 - 1.4930 - 59.997090
8925 - 49.995 - 29.990 - 1.4930 - 59.997091
9025 - 49.9930 - 49.990 - 1.4930 - 59.997092
9125 - 49.99≥500 - 1.4930 - 59.99
9250 - 74.990 - 4.990 - 1.4930 - 59.997095
9350 - 74.995 - 29.990 - 1.4930 - 59.997096
9450 - 74.99≥300 - 1.4930 - 59.99
95>750 - 4.990 - 1.4930 - 59.99
96>75≥50 - 1.4930 - 59.99
Sheet1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Morning Micron,

thanks, I'll have a read through and post back here if I need any assistance otherwise I'll marked it as closed later.
 
Upvote 0
Had a look at this, I think it's something to do with how my data is set up (Spaces and special characters) as I can't appear to get it to work.

I worked through the examples and it worked fine.
 
Upvote 0
Closing the thread as I found a way around this using UNIQUE(FILTER on the 1st & 3rd drop downs.

Solution i've used (It's not pretty but does the job):

in H21: A drop down based on the UNIQUE values that appear in Col A

in H22: =UNIQUE(FILTER(A:B,A:A=H21)) - Filters the unique Sucrose content dependent on the Starch content.

In I21: Simple drop down list based upon the Dynamic return of the filter

in J21: Drop down based upon the unique values in Col C

in J22: =UNIQUE(FILTER(C:D,C:C=J21)) - Filters the unique Milk protein contents dependent on the Milk Fat content.

in L21: =IF(OR(H21="",I21="",J21="",K21=""),"",XLOOKUP(1,(A:A=H21)*(B:B=I21)*(C:C=J21)*(D:D=K21),E:E)) to return the Meursing code form the table.


This is just my testing area, I'll probably hide the rows to which the FILTERS return into.


1735897992166.png
 
Last edited:
Upvote 0
Solution
Good to hear you got a solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
The mark at the right of a post is to mark a solution, not to close the thread. We don't close threads here as even after a good solution has been posted it is always possible that a better one may get posted subsequently. :)
 
Upvote 0
Peter_SSs said:
Good to hear you got a solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
The mark at the right of a post is to mark a solution, not to close the thread. We don't close threads here as even after a good solution has been posted it is always possible that a better one may get posted subsequently. :)
Click to expand...

apologies, updated my post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Generating Number Range Value - (INDEX SUMPRODUCT ROW?)
Replies
3
Views
562
Toadstool
Toadstool
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
VBA Copy from depend drop downs
Replies
0
Views
750
SlightlyClueless
S
D
  • Question Question
Difficult Excel Formula - Populating Data from Drop Down Menu
Replies
0
Views
608
DataValidator
D
E
  • Question Question
Using drop down list to populate cells
2 3
Replies
24
Views
2K
mumps
mumps

Forum statistics

Threads
1,225,327
Messages
6,184,296
Members
453,227
Latest member
Slainte

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