Easiest way to have a dropdown based on 2 conditions

FJPunisher

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Hello Experts,

I am looking for help on below, I am sure this is possible but can’t find a most efficient way to do this.
  • I will capture ID as text in a cell, this can be A_100, A_200, A_300, A_400, A_500 or A_600, (DONE)
  • Based on that ID, I am using VB to paste the days that are associated with that ID, (DONE)
  • Now based on the ID & DAY i want to show a dropdown list with up to three options. (HELP!)
In short, what is the easiest way to have dropdown list based on 2 conditions…
e.g.
IF ID = A_100 & Day = D_SUN Then Dropdown = 5.50, 6.6, 4.5
 

Attachments

  • Screenshot 2021-03-22 203210.jpg
    Screenshot 2021-03-22 203210.jpg
    41.2 KB · Views: 5

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
With a helper column, I tried this:

ReplicateProductsBYCusomers.xlsx
GHIJKLMNO
1A_100D_Thur
2A_100D_Sun5.506.64.510
3A_100D_Mon2.002.23.58.1
4A_100D_Tue6.2
5A_100D_Wed
6A_100D_Thur10.008.16.2
7A_100D_Fri
8A_200D_Sun
9A_200D_Mon
10A_200D_Tue
11A_200D_Wed
12A_200D_Thur
13A_200D_Fri
Sheet4
Cell Formulas
RangeFormula
O2:O4O2=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.


1616450675293.png


1616450707929.png
 
Upvote 0
Hi Kweaver,

Thank you for this, truly appreciate it!



However, it didn’t work for me, not sure what I am doing wrong (Screenshot one).

In an effort to simplify the data to meet other requirements, I have made some changes to the table, would appreciate if you can update your solution and also point out anything I am doing wrong as below didn’t work for me:



O2
=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
=INDIRECT(TRANSPOSE((INDEX($G$3:$K$29,MATCH($M$1&$N$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3}))),FALSE)



Data Validation
=O2#
=INDIRECT(TRANSPOSE((INDEX($G$3:$K$29,MATCH($M$1&$N$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3}))),FALSE)
=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))



Book1.xlsx
FGHIJKLMNOPQR
1I will use VLOOKUP for thisA_100D_Wed
2IDPRO.IDDAYDropdownOther Data AOther Data BOther Data C#VALUE!
3A_100D_SunOption 15.50
4A_100D_SunOption 26.6
5A_100D_SunOption 34.5
6A_100D_MonOption 12.00
7A_100D_MonOption 22.2
8A_100D_MonOption 33.5
9A_100D_TueOption 13.50
10A_100D_TueOption 20
11A_100D_TueOption 30
12A_100D_WedOption 10.10
13A_100D_WedOption 22.4
14A_100D_WedOption 31.2
15A_100D_ThurOption 10.00
16A_100D_ThurOption 24.5
17A_100D_ThurOption 35.50
18A_100D_FriOption 12.00
19A_100D_FriOption 23.5
20A_100D_FriOption 32.00
21A_200D_SunOption 11.50
22A_200D_SunOption 21.2
23A_200D_SunOption 32.1
24A_200D_MonOption 11.80
25A_200D_MonOption 20
26A_200D_MonOption 32.1
27A_200D_ThurOption 10.00
28A_200D_ThurOption 22.1
29A_200D_ThurOption 31.80
Sheet1 (2)
Cell Formulas
RangeFormula
R2R2=TRANSPOSE((INDEX($G$3:$K$29,MATCH($P$1&$Q$1,$G$3:$G$29&$I$3:$I$29,0),{1,2,3})))
Named Ranges
NameRefers ToCells
'Sheet1 (2)'!A_100D_Sun='Sheet1 (2)'!$K$10:$M$10R2
Cells with Data Validation
CellAllowCriteria
O8List=INDIRECT(#REF!)
 

Attachments

  • 1.jpg
    1.jpg
    80.5 KB · Views: 4
Upvote 0
With a helper column, I tried this:

ReplicateProductsBYCusomers.xlsx
GHIJKLMNO
1A_100D_Thur
2A_100D_Sun5.506.64.510
3A_100D_Mon2.002.23.58.1
4A_100D_Tue6.2
5A_100D_Wed
6A_100D_Thur10.008.16.2
7A_100D_Fri
8A_200D_Sun
9A_200D_Mon
10A_200D_Tue
11A_200D_Wed
12A_200D_Thur
13A_200D_Fri
Sheet4
Cell Formulas
RangeFormula
O2:O4O2=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.


View attachment 35035

View attachment 35036

Thanks Kweaver!
 
Upvote 0
I'm afraid I don't understand what you now want to do as you've totally changed the layout from the original post.
 
Upvote 0
If you make R3:
Code:
=FILTER(K3:K38,($P$1&$Q$1=G3:G38&I3:I38))
and reference a drop down in, say, P2 using =R3# does that work?
(change the 38 as needed)
 
Upvote 0
I get an error saying Function is not valid.
Do you know what i am doing wrong in the original solution you suggested?

What does Range 02:04 mean in your original post


RangeFormula
Cell Formulas
O2:O4O2=TRANSPOSE((INDEX($I$2:$K$13,MATCH($M$1&$N$1,$G$2:$G$13&$H$2:$H$13,0),{1,2,3})))
Dynamic array formulas.


Mine is :
RangeFormula
Cell Formulas
R2R2=TRANSPOSE((INDEX($G$3:$K$29,MAT

Also the below source (=02#) comes up as invalid, =INDIRECT(O2) is also not working for me in dropdown sournce.

1616612093140.png
 

Attachments

  • Screenshot 2021-03-24 185142.jpg
    Screenshot 2021-03-24 185142.jpg
    36 KB · Views: 2
Upvote 0
Ah, Excel 2019 probably doesn't have FILTER. Sorry about that.

What does Range 02:04 mean in your original post? That's an "O" and not a "0". Cells O2:O4
 
Upvote 0
Just figured out, i wasnt using crt shift enter, your first solution is working now, but gives me one one option...
 

Attachments

  • 123.jpg
    123.jpg
    66.1 KB · Views: 2
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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