Create a drop down menu that relies on the first drop down menu selection to drive a price result.

N

Neilads

New Member
Joined
Jun 29, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello

I have an invoice template that I would like to set up to save time when choosing a product, so that it will display the size chosen and then the price associated with it.

Can you help?!

Book3
ABC
1ProductsSizePrice
2
3Long Rumper0-3 / 3-6 / 6-9£18.00
49-12 / 12-18 / 18-24£20.00
52-3 / 3-4 / 4-5£22.00
6Short Rumper0-3 / 3-6 / 6-9£16.00
79-12 / 12-18 / 18-24£18.00
82-3 / 3-4 / 4-5£20.00
9Short Dungarees0-3 / 3-6 / 6-9£18.00
109-12 / 12-18 / 18-24£20.00
112-3 / 3-4 / 4-5£22.00
12Long Dungarees0-3 / 3-6 / 6-9£20.00
139-12 / 12-18 / 18-24£22.00
142-3 / 3-4 / 4-5£24.00
15Storage Bags1£7.00
162£12.00
17Shorts0-3 / 3-6 / 6-9£8.00
189-12 / 12-18 / 18-24£10.00
192-3 / 3-4 / 4-5£12.00
20Bummies0-3 / 3-6 / 6-9£8.00
219-12 / 12-18 / 18-24£10.00
222-3 / 3-4 / 4-5£12.00
23Head Bands1£6.00
242£10.00
25Leggings0-3 / 3-6 / 6-9£10.00
269-12 / 12-18 / 18-24£12.00
272-3 / 3-4 / 4-5£14.00
28Bonnets0-2 / 3-6 / 6-12£15.00
2912-18 / 18-24£15.00
30Large Bow Headband1£4.00
31Set of 2£7.00
32Large Bow Hair Ties1£4.00
33Set of 2£7.00
34Button Hair ClipOne Pack (2)£3.50
35Two Packs (4)£6.00
36Button Hair TiesOne Pack (3)£5.00
37Two Packs (6)£9.00
38Dresses0-3 / 3-6 / 6-9£14.00
399-12 / 12-18 / 18-24£16.00
402-3 / 3-4 / 4-5£18.00
41T Shirts0-3 / 3-6 / 6-9£10.00
429-12 / 12-18 / 18-24£12.00
432-3 / 3-4 / 4-5£14.00
44Face WipesSmall£4.00
45Large£8.00
46PostageLocal Collection£0.00
47UK£2.00
Products


and then the sheet that has the drop down menus that needs to read the first sheet and return the right result.
In the size column, it needs to be dependent on the product, and only return the values that correspond to the product. And the price column, would show the correct price automatically, based on the product and the size.

Book3
ABCDE
1
2Invoice No.:1
3Date:
4Reference No.:
5
6
7
8
9ProductSizeQuantityUnit PriceTotal Price
10Long Rumper1$0.00$0.00
11
12
13
14
15
16
17Total$0.00
Invoice
Cell Formulas
RangeFormula
E10E10=D10*C10
E17E17=SUM(E10:E16)
Cells with Data Validation
CellAllowCriteria
A10List=Products!$A$3:$A$47
B10List=Products!$B$3:$B$47



If I haven't explained anything well, please do let me know!
Thank you in advance for your help
Neil
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Ok so what you are describing is dependent dropdowns. You can achieve this will data validation and some formulas.

Here is the sample Invoice I created with the two dropdowns and a price lookup
Book4
ABC
1
2
3Face WipesSmall4
4
5
Invoice
Cell Formulas
RangeFormula
C3C3=IF(NOT(ISERROR(VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","_")&"_Price"),2,0))),VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","_")&"_Price"),2,0),0)
Cells with Data Validation
CellAllowCriteria
A3List=Products
B3List=INDIRECT(SUBSTITUTE(A3," ","_"))


The dropdowns are dependent on this worksheet which has all the products, sizes, and prices parsed out so that they can be named ranges.
Book4
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBC
1ProductsLong RumperShort RumperShort DungareesLong DungareesStorage BagsShortsBummiesHead BandsLeggingsBonnetsLarge Bow HeadbandLarge Bow Hair TiesButton Hair ClipButton Hair TiesDressesT ShirtsFace WipesPostage
2BonnetsSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePriceSizePrice
3Bummies0-3 / 3-6 / 6-9180-3 / 3-6 / 6-9160-3 / 3-6 / 6-9180-3 / 3-6 / 6-920170-3 / 3-6 / 6-980-3 / 3-6 / 6-98160-3 / 3-6 / 6-9100-2 / 3-6 / 6-12151414One Pack (2)3.5One Pack (3)50-3 / 3-6 / 6-9140-3 / 3-6 / 6-910Small4Local Collection0
4Button Hair Clip9-12 / 12-18 / 18-24209-12 / 12-18 / 18-24189-12 / 12-18 / 18-24209-12 / 12-18 / 18-24222129-12 / 12-18 / 18-24109-12 / 12-18 / 18-24102109-12 / 12-18 / 18-241212-18 / 18-2415Set of 27Set of 27Two Packs (4)6Two Packs (6)99-12 / 12-18 / 18-24169-12 / 12-18 / 18-2412Large8UK2
5Button Hair Ties2-3 / 3-4 / 4-5222-3 / 3-4 / 4-5202-3 / 3-4 / 4-5222-3 / 3-4 / 4-5242-3 / 3-4 / 4-5122-3 / 3-4 / 4-5122-3 / 3-4 / 4-5142-3 / 3-4 / 4-5182-3 / 3-4 / 4-514
6Dresses
7Face Wipes
8Head Bands
9Large Bow Hair Ties
10Large Bow Headband
11Leggings
12Long Dungarees
13Long Rumper
14Postage
15Short Dungarees
16Short Rumper
17Shorts
18Storage Bags
19T Shirts
20
Sheet1


If you really want to get fancy you can incorporate code in the worksheet change event that will clear the size field when the product changest as follows:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0, 1).Value = ""
    End If
End Sub

You should probably be more strict with the range of cells the target must fall into, but hopefully you get the idea.
 
Upvote 0
Here's a version using Excel 365 functions. The fact that you have used merged cells for the products makes it more difficult. The K2 and L2 formulas are helper cells.

Book1
ABCDEFGHIJKL
1ProductsSizePriceProductSizeUnit PriceQtyTotal PriceProductsSize
2Dresses9-12 / 12-18 / 18-2416232Long Rumper0-3 / 3-6 / 6-9
3Long Rumper0-3 / 3-6 / 6-918Short Rumper9-12 / 12-18 / 18-24
49-12 / 12-18 / 18-2420Short Dungarees2-3 / 3-4 / 4-5
52-3 / 3-4 / 4-522Long Dungarees
6Short Rumper0-3 / 3-6 / 6-916Storage Bags
79-12 / 12-18 / 18-2418Shorts
82-3 / 3-4 / 4-520Bummies
9Short Dungarees0-3 / 3-6 / 6-918Head Bands
109-12 / 12-18 / 18-2420Leggings
112-3 / 3-4 / 4-522Bonnets
12Long Dungarees0-3 / 3-6 / 6-920Large Bow Headband
139-12 / 12-18 / 18-2422Large Bow Hair Ties
142-3 / 3-4 / 4-524Button Hair Clip
15Storage Bags17Button Hair Ties
16212Dresses
17Shorts0-3 / 3-6 / 6-98T Shirts
189-12 / 12-18 / 18-2410Face Wipes
192-3 / 3-4 / 4-512Postage
20Bummies0-3 / 3-6 / 6-98
219-12 / 12-18 / 18-2410
222-3 / 3-4 / 4-512
23Head Bands16
24210
25Leggings0-3 / 3-6 / 6-910
269-12 / 12-18 / 18-2412
272-3 / 3-4 / 4-514
28Bonnets0-2 / 3-6 / 6-1215
2912-18 / 18-2415
30Large Bow Headband14
31Set of 27
32Large Bow Hair Ties14
33Set of 27
34Button Hair ClipOne Pack (2)3.5
35Two Packs (4)6
36Button Hair TiesOne Pack (3)5
37Two Packs (6)9
38Dresses0-3 / 3-6 / 6-914
399-12 / 12-18 / 18-2416
402-3 / 3-4 / 4-518
41T Shirts0-3 / 3-6 / 6-910
429-12 / 12-18 / 18-2412
432-3 / 3-4 / 4-514
44Face WipesSmall4
45Large8
46PostageLocal Collection0
47UK2
Sheet12
Cell Formulas
RangeFormula
G2G2=INDEX(INDEX(C3:C100,MATCH(E2,A3:A100,0)):C100,MATCH(F2,INDEX(B3:B100,MATCH(E2,A3:A100,0)):B100,0))
I2I2=G2*H2
K2:K19K2=FILTER(A3:A100,A3:A100<>"")
L2:L4L2=FILTER(B3:B100,(ROW(C3:C100)>=MATCH(E2,A3:A100,0)+ROW(A3)-1)*(ROW(C3:C100)<IFERROR(MATCH(INDEX(K2#,MATCH(E2,K2#,0)+1),A3:A300,0)+ROW(A3)-1,9^9))*(B3:B100<>""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E2:F2List=K2#
 
Upvote 0
@Eric W WOW!!! I feel like a dinosaur!! That is amazing!! Clearly the easier and more efficient approach.

Just some house keeping. @Neilads you may still want to incorporate the Worksheet_Change code to clear invalid size values and also error checking in the formulas for Unit Price and Total Price so you don't see nasty errors when changing products.
 
Upvote 0
Crystalyzer said:
@Eric W WOW!!! I feel like a dinosaur!! That is amazing!! Clearly the easier and more efficient approach.
Click to expand...
Yes, those new functions are really great. I feel like the Excel community is still figuring out ways to use them. But my G and L formulas would have been much shorter without the merged cells, so a cleaned up layout like you did would still be beneficial. And I agree, your Change event is a nice touch.
 
Upvote 0
Thanks Eric and Crystalyzer,

I have a copied both sets to try out.
Eric, I can't make the drop down menu for size work. I have to manually enter in the size. I can't work out what column L is doing.
In fact - that's my main problem, the formulas are so clever, that I don't really know what I am looking at!

I'll keep going as I can make it the basic functions but get stuck when more than 1 product would be bought to layout on 1 invoice.

Thanks!
Neil
 
Upvote 0
If you can show me your worksheets with your attempts at the formulas, I'll see what's up with the size formula.

And just to show how much easier it would be if you repeated the product name instead of putting it in a merged cell, here's how that would look:

Book1
ABCDEFGHIJKLMNOP
1ProductsSizePriceProductSizeUnit PriceQtyTotal PriceProductsSize1Size2Size3Size4Size5
2Dresses9-12 / 12-18 / 18-2416232Long Rumper0-3 / 3-6 / 6-90-3 / 3-6 / 6-91  
3Long Rumper0-3 / 3-6 / 6-918Shorts2-3 / 3-4 / 4-512336Short Rumper9-12 / 12-18 / 18-249-12 / 12-18 / 18-242
4Long Rumper9-12 / 12-18 / 18-2420Storage Bags212112Short Dungarees2-3 / 3-4 / 4-52-3 / 3-4 / 4-5
5Long Rumper2-3 / 3-4 / 4-522  Long Dungarees
6Short Rumper0-3 / 3-6 / 6-916  Storage Bags
7Short Rumper9-12 / 12-18 / 18-2418Shorts
8Short Rumper2-3 / 3-4 / 4-520Bummies
9Short Dungarees0-3 / 3-6 / 6-918Head Bands
10Short Dungarees9-12 / 12-18 / 18-2420Leggings
11Short Dungarees2-3 / 3-4 / 4-522Bonnets
12Long Dungarees0-3 / 3-6 / 6-920Large Bow Headband
13Long Dungarees9-12 / 12-18 / 18-2422Large Bow Hair Ties
14Long Dungarees2-3 / 3-4 / 4-524Button Hair Clip
15Storage Bags17Button Hair Ties
16Storage Bags212Dresses
17Shorts0-3 / 3-6 / 6-98T Shirts
18Shorts9-12 / 12-18 / 18-2410Face Wipes
19Shorts2-3 / 3-4 / 4-512Postage
20Bummies0-3 / 3-6 / 6-98
21Bummies9-12 / 12-18 / 18-2410
22Bummies2-3 / 3-4 / 4-512
23Head Bands16
24Head Bands210
25Leggings0-3 / 3-6 / 6-910
26Leggings9-12 / 12-18 / 18-2412
27Leggings2-3 / 3-4 / 4-514
28Bonnets0-2 / 3-6 / 6-1215
29Bonnets12-18 / 18-2415
30Large Bow Headband14
31Large Bow HeadbandSet of 27
32Large Bow Hair Ties14
33Large Bow Hair TiesSet of 27
34Button Hair ClipOne Pack (2)3.5
35Button Hair ClipTwo Packs (4)6
Sheet12
Cell Formulas
RangeFormula
K2:K19K2=UNIQUE(FILTER(A3:A100,A3:A100<>""))
L2:L4L2=IF(E2="","",FILTER($B$3:$B$100,$A$3:$A$100=E2))
M2:M4M2=IF(E3="","",FILTER($B$3:$B$100,$A$3:$A$100=E3))
N2:N3N2=IF(E4="","",FILTER($B$3:$B$100,$A$3:$A$100=E4))
O2O2=IF(E5="","",FILTER($B$3:$B$100,$A$3:$A$100=E5))
P2P2=IF(E6="","",FILTER($B$3:$B$100,$A$3:$A$100=E6))
G2:G6G2=IFERROR(FILTER(C$3:C$100,(A$3:A$100=E2)*(B$3:B$100=F2)*(C$3:C$100<>"")),"")
I2:I6I2=IFERROR(G2*H2,"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E2:E6List=$K$2#
F2List=L2#
F3List=M2#
F4List=N2#
F5List=O2#
F6List=P2#


The K2 formula can be repeated for multiple line items in column E. But since the sizes would vary, you'd need a different formula for each line in column F.
 
Upvote 0
Solution
Hi Eric,

That is perfect! I can manipulate it now to look live an invoice for customers.

Thank you so much for your help to resolve this tricky puzzle!

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

Similar threads

A
  • Question
Excel Formula.
Replies
1
Views
181
StephenCrump
S
A
  • Question
I want to fill the matrix next to Hour Out with the headcount based on the data from the first 3 columns. Please help!! :'(
Replies
0
Views
56
AliaNi
A
I
  • Question
Tracking vacation requests
Replies
9
Views
423
KRice
K
S
  • Question
Check if a previously drawn number is in a group.
Replies
1
Views
260
Excel Novice 1
E
E
  • Question
Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.
Replies
4
Views
100
Event2020
E

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

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