Drop down menu with values

[SJSB]

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.

 

[kweaver]

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]

OR
Upload the workbook to a hosting site, DropBox for instance, then post the link back here.

 

[SJSB]

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
	A	B	C	D	E	F	G	H	I
1			BASE	Income from Property No of properties	Tax	Payroll	CIS	VAT	TOTAL
2	INDIVIDUAL			(Number)	(Yes/No)	(Number)	(Number)	(No/Review/More)
3	Simple	Employment	120
4	Simple	Employment (+ expenses)
5	SOLE TRADER
6		Simple	200
7		Complex	500
8	PARTNERSHIP
9		FHL/Lord	300
10		Trading	500
11	COMPANY
12	Simple	Dormant	100
13	Simple	Property (1 director)	500
14	Simple	Trading	650	2	Yes	2	2	Review	£1,420
15	Complex	Trading	850
16
17	OTHER
18		Payroll (1 person)	150
19		Payroll per other > 1	50
20		CIS (1 person)	150
21		CIS per other > 1	50
22		VAT review only	200
23		VAT more work	400
24		Income from property (1 property)	100
25		Income from property per other > 1 property	70
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D3:D16	List	0, 1, 2, 3, 4, 5
E3:E28	List	Yes, No
F3:H16	Any value
F17:H25	List	Yes, No

 

[jtakw]

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]

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
	A	B	C	D	E	F	G	H	I
2	INDIVIDUAL			(Number)	(Yes/No)	(Number)	(Number)	(No/Review/More)
3	Simple	Employment	120	2		2	2	Review	890
4	Simple	Employment (+ expenses)		2		2	2	Review	770
5	SOLE TRADER
6		Simple	200						200
7		Complex	500						500
8	PARTNERSHIP
9		FHL/Lord	300						300
10		Trading	500						500
11	COMPANY
12	Simple	Dormant	100						100
13	Simple	Property (1 director)	500						500
14	Simple	Trading	650	2	Yes	2	2	Review	1420
15	Complex	Trading	850						850
16
17	OTHER
18		Payroll (1 person)	150
19		Payroll per other > 1	50
20		CIS (1 person)	150
21		CIS per other > 1	50
22		VAT review only	200
23		VAT more work	400
24		Income from property (1 property)	100
25		Income from property per other > 1 property	70
Sheet921

Cell Formulas
Range	Formula
I3:I15	I3	=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
Cell	Allow	Criteria
D3:D15	List	1,2,3,4,5
E3:E15	List	Yes,No
H3:H15	List	No,Review,More

 

[SJSB]

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
	A	B	C	D	E	F	G	H	I
2	INDIVIDUAL			(Number)	(Yes/No)	(Number)	(Number)	(No/Review/More)
3	Simple	Employment	120	2		2	2	Review	890
4	Simple	Employment (+ expenses)		2		2	2	Review	770
5	SOLE TRADER
6		Simple	200						200
7		Complex	500						500
8	PARTNERSHIP
9		FHL/Lord	300						300
10		Trading	500						500
11	COMPANY
12	Simple	Dormant	100						100
13	Simple	Property (1 director)	500						500
14	Simple	Trading	650	2	Yes	2	2	Review	1420
15	Complex	Trading	850						850
16
17	OTHER
18		Payroll (1 person)	150
19		Payroll per other > 1	50
20		CIS (1 person)	150
21		CIS per other > 1	50
22		VAT review only	200
23		VAT more work	400
24		Income from property (1 property)	100
25		Income from property per other > 1 property	70
Sheet921

Cell Formulas
Range	Formula
I3:I15	I3	=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
Cell	Allow	Criteria
D3:D15	List	1,2,3,4,5
E3:E15	List	Yes,No
H3:H15	List	No,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]

You're welcome, thanks for the feedback.