Drop down menu with values

SJSB

New Member
Joined
Apr 16, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
I am trying to create a spreadsheet to calculate pricing based on variables for different categories.
I would like the drop down options to be as in row 2 - either a number or a multiple choice.

Depending on what is selected, it would have a different value - eg only 1 property would be 100 but two, would be 170 (100+70 and then 3 properties would be 240).

Can someone please point me in the direction of where to find how to do this?
I don't even know what terms to google!

Huge thanks,
Sasha.


1618607140709.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel.

Please use XL2BB when showing a sample so that someone trying to solve this for you doesn't have to retype your data.
A few examples of what the dropdown would show and what you want and where you want the result would help as well.

That being said, do you mean ROW 2 (which has "individual" blank (Number) Yes/No etc.) or do you mean column 2 as in column B?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
OR
Upload the workbook to a hosting site, DropBox for instance, then post the link back here.
 

SJSB

New Member
Joined
Apr 16, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Welcome to Mr. Excel.

Please use XL2BB when showing a sample so that someone trying to solve this for you doesn't have to retype your data.
A few examples of what the dropdown would show and what you want and where you want the result would help as well.

That being said, do you mean ROW 2 (which has "individual" blank (Number) Yes/No etc.) or do you mean column 2 as in column B?
Thank you for the warm welcome.

I have now added the minisheet below.

I mean: In ROW 2 - the items in the pink row, below the column headings (2d-2h) are what I would like the drop downs to be. Ie, the drop down menu for Column E (Tax) would give the options for 'Yes' or 'No'. For Column H (VAT), the options would be 'No', 'Review' or 'More'.

In the other columns (d,f,g) maybe this isn't a dropdown but you just insert a numeric value. This number then creates a new sum based on the figures in the 'OPTIONS' section at the bottom of the sheet you can see.

Does that make sense?

Huge thanks.


Pricing matrix_Mr Excel.xlsx
ABCDEFGHI
1BASEIncome from Property No of propertiesTaxPayrollCISVATTOTAL
2INDIVIDUAL(Number)(Yes/No)(Number)(Number)(No/Review/More)
3SimpleEmployment120
4SimpleEmployment (+ expenses)
5SOLE TRADER
6Simple200
7Complex500
8PARTNERSHIP
9FHL/Lord300
10Trading500
11COMPANY
12SimpleDormant100
13SimpleProperty (1 director)500
14SimpleTrading6502Yes22Review£1,420
15ComplexTrading850
16
17OTHER
18Payroll (1 person)150
19Payroll per other > 150
20CIS (1 person)150
21CIS per other > 150
22VAT review only200
23VAT more work400
24Income from property (1 property)100
25Income from property per other > 1 property70
Sheet1
Cells with Data Validation
CellAllowCriteria
D3:D16List0, 1, 2, 3, 4, 5
E3:E28ListYes, No
F3:H16Any value
F17:H25ListYes, No
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,

I'm not sure what your question(s) are.
So, are you needing a formula for Column I "Total"?
Or, are you asking for help to create the Drop Downs for Columns D, E, & H ?

Also, Your B18:C25 has no provisions for Column E "Tax"?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Looking at your Post #4 above, looks like you have some of your Drop Down list set up, so assuming you already know how to do that, and is looking for a formula for Column I
Try this, just change the figures in C18:C25 if it changes in the future, I've used these Cell References for the formula, again, you have no provisions for "Tax", so might need to modify:

Book3.xlsx
ABCDEFGHI
2INDIVIDUAL(Number)(Yes/No)(Number)(Number)(No/Review/More)
3SimpleEmployment120222Review890
4SimpleEmployment (+ expenses)222Review770
5SOLE TRADER 
6Simple200200
7Complex500500
8PARTNERSHIP 
9FHL/Lord300300
10Trading500500
11COMPANY 
12SimpleDormant100100
13SimpleProperty (1 director)500500
14SimpleTrading6502Yes22Review1420
15ComplexTrading850850
16
17OTHER
18Payroll (1 person)150
19Payroll per other > 150
20CIS (1 person)150
21CIS per other > 150
22VAT review only200
23VAT more work400
24Income from property (1 property)100
25Income from property per other > 1 property70
Sheet921
Cell Formulas
RangeFormula
I3:I15I3=IF(B3="","",C3+IF(D3=0,0,(D3-1)*C$25+C$24)+IF(F3=0,0,(F3-1)*C$19+C$18)+IF(G3=0,0,(G3-1)*C$21+C$20)+IF(OR(H3={"No",""}),0,IF(H3="Review",C$22,C$23)))
Cells with Data Validation
CellAllowCriteria
D3:D15List1,2,3,4,5
E3:E15ListYes,No
H3:H15ListNo,Review,More
 
Solution

SJSB

New Member
Joined
Apr 16, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Looking at your Post #4 above, looks like you have some of your Drop Down list set up, so assuming you already know how to do that, and is looking for a formula for Column I
Try this, just change the figures in C18:C25 if it changes in the future, I've used these Cell References for the formula, again, you have no provisions for "Tax", so might need to modify:

Book3.xlsx
ABCDEFGHI
2INDIVIDUAL(Number)(Yes/No)(Number)(Number)(No/Review/More)
3SimpleEmployment120222Review890
4SimpleEmployment (+ expenses)222Review770
5SOLE TRADER 
6Simple200200
7Complex500500
8PARTNERSHIP 
9FHL/Lord300300
10Trading500500
11COMPANY 
12SimpleDormant100100
13SimpleProperty (1 director)500500
14SimpleTrading6502Yes22Review1420
15ComplexTrading850850
16
17OTHER
18Payroll (1 person)150
19Payroll per other > 150
20CIS (1 person)150
21CIS per other > 150
22VAT review only200
23VAT more work400
24Income from property (1 property)100
25Income from property per other > 1 property70
Sheet921
Cell Formulas
RangeFormula
I3:I15I3=IF(B3="","",C3+IF(D3=0,0,(D3-1)*C$25+C$24)+IF(F3=0,0,(F3-1)*C$19+C$18)+IF(G3=0,0,(G3-1)*C$21+C$20)+IF(OR(H3={"No",""}),0,IF(H3="Review",C$22,C$23)))
Cells with Data Validation
CellAllowCriteria
D3:D15List1,2,3,4,5
E3:E15ListYes,No
H3:H15ListNo,Review,More
Thank you so much. You're so clever! It looks like this is exactly what I am trying to do! Can you please tell me where I can look it up so I can learn how to do it myself? Ie what this function is called? I found how to make the drop downs but I don't know how to make values for them and for them to then make totals. Thank you for your expertise, I really appreciate you sharing your knowledge.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Forum statistics

Threads
1,144,689
Messages
5,725,784
Members
422,640
Latest member
KazPL

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
Top