How to put a formula in a cell if a particular item is selected from drop-down list?

[shadab841]

Okay, I work in a logistic company
and there is an excel sheet and i have to update the shipment cost and shipment type.
The thing is that there are three type of shipments


EU. Vat is applicable on this shipment

Non-Eu. No Vat

Domestic. Vat is applicable

So for the selection of shipment type in the excel sheet there is a drop-down list from where i have to select Eu shipment or Non-Eu shipment.

What I want is when I select EU or domestic the Vat row automatically calculates vat from the net value that is 20% of Net value.

And when i select domestic it automatically shows 0.

Is that possible, I've tried a lot but failed also am new to excel.

 

[etaf]

=IF( B2 = "Non Eu", 0 , C2*0.2)
Assuming its 20% for the other 2 dropdowns

 

[shadab841]

=IF( B2 = "Non Eu", 0 , C2*0.2)
Assuming its 20% for the other 2 dropdowns

Yes.

Can i use this in data validation?

Let me check normally btw thanks a lot bro I've been screwing with this for a long time.

 

[etaf]

What do you want to use in data validation - the list ?
YES

Book3
	B	C	D	E	F	G
1	Shipment type	NET	VAT			Data Validation List
2	NON EU	100	0			EU
3	EU	1000	200			NON EU
4	NON EU	10000	0			DOMESTIC
5	DOMESTIC	100000	20000
6		1000000
7		10000000
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(B2="", "", IF( B2 = "Non Eu", 0, C2*0.2))

Cells with Data Validation
Cell	Allow	Criteria
B2:B7	List	=$G$2:$G$4

 

[shadab841]

What do you want to use in data validation - the list ?
YES

Book3
	B	C	D	E	F	G
1	Shipment type	NET	VAT			Data Validation List
2	NON EU	100	0			EU
3	EU	1000	200			NON EU
4	NON EU	10000	0			DOMESTIC
5	DOMESTIC	100000	20000
6		1000000
7		10000000
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(B2="", "", IF( B2 = "Non Eu", 0, C2*0.2))

Cells with Data Validation
Cell	Allow	Criteria
B2:B7	List	=$G$2:$G$4

You're the boss bro.
Thank you so much

maybe I don't know much about data validation but i was thinking that if that is possible so my cells remains empty as if there is no formula there but when i select eu shipment it calculates in the vat column as through data validation you can put the formulas by selecting custom.

Please correct me if i am wrong?

 

[shadab841]

You're the boss bro.
Thank you so much

maybe I don't know much about data validation but i was thinking that if that is possible so my cells remains empty as if there is no formula there but when i select eu shipment it calculates in the vat column as through data validation you can put the formulas by selecting custom.

Please correct me if i am wrong?

and is it possible if I by mistake calculate vat on NON EU shipment deleting the formula and it shows me a warning that "This is a NON-EU Shipment. Vat isn't applicable on this type of shipment" like in data validation we can put a warning message.

 

[etaf]

maybe I don't know much about data validation but i was thinking that if that is possible so my cells remains empty as if there is no formula there but when i select eu shipment it calculates in the vat column as through data validation you can put the formulas by selecting custom.

That is the IF(B2="", ""
If blank then result is blank

 

[etaf]

and is it possible if I by mistake calculate vat on NON EU shipment deleting the formula and it shows me a warning that "This is a NON-EU Shipment. Vat isn't applicable on this type of shipment" like in data validation we can put a warning message.

How would excel know that it should NOT be a NON EU ? and VAT is not Applicable , and if it knows that , then why do you choose the dropdown, as it can be done automatically based on the "other" criteria that identifies a mistake