How to change the Formula, based on a Value in a List

[WayneGoodrich]

I have an excel spreadsheet with a Validated List with 4 different Values.
I want to change the Formula used in Row M based on the Value chosen in Row D
These are the list Values and formulas I want to choose, after selecting List Value

List Value	Formula
Continuous Unlined	"=(K10*14)+8"
Drops Unlined	"=((K10*14)+8)+(L10*6)"
Continuous Lined	"=(K10*20)+9"
Drops Lined	"=((K10*20)+9)+(L10*12)"

Any help would be appreciated,
Regards
Wayne

 

[JoeMo]

In M10 and copy down:

=IF(D10="Continuous Unlined",(K10*14)+8,IF(D10="Drops Unlined",(K10*14+8)+L10*6,IF(D10="Continuous Lined",K10*20+9,IF(D10="Drops Lined",(K10*20+9)+L10*12,"Not on the list"))))

 

[WayneGoodrich]

In M10 and copy down:

=IF(D10="Continuous Unlined",(K10*14)+8,IF(D10="Drops Unlined",(K10*14+8)+L10*6,IF(D10="Continuous Lined",K10*20+9,IF(D10="Drops Lined",(K10*20+9)+L10*12,"Not on the list"))))

Hi JoeMo,
I am probably doing something wrong, but when I copy your formula in, I always get the answer "Not on the List"

In M10 and copy down:

=IF(D10="Continuous Unlined",(K10*14)+8,IF(D10="Drops Unlined",(K10*14+8)+L10*6,IF(D10="Continuous Lined",K10*20+9,IF(D10="Drops Lined",(K10*20+9)+L10*12,"Not on the list"))))

Got it Right, Thanks for your help- Perfect solution, Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Other structures that you could use would be ..

=CHOOSE(MATCH(D10,{"Continuous Unlined","Drops Unlined","Continuous Lined","Drops Lined"},0),K10*14+8,K10*14+8+L10*6,K10*20+9,K10*20+9+L10*12)

.. or even

=LET(L,RIGHT(D10,6)=" Lined",D,LEFT(D10,1)="D",K10*(14+6*L)+8+L+L10*6*(1+L)*D)

Examples with all formulas

22 06 05.xlsm
	D							K	L	M	N	O
9										JoeMo	Peter_SSs1	Peter_SSs2
10	Continuous Unlined							8	0	120	120	120
11	Drops Unlined							8	5	150	150	150
12	Continuous Lined							8	0	169	169	169
13	Drops Lined							8	5	229	229	229
14	Continuous Unlined							7	4	106	106	106
15	Drops Unlined							18	3	278	278	278
16	Continuous Lined							5	26	109	109	109
17	Drops Lined							14	9	397	397	397
CHOOSE or LET

Cell Formulas
Range	Formula
M10:M17	M10	=IF(D10="Continuous Unlined",(K10*14)+8,IF(D10="Drops Unlined",(K10*14+8)+L10*6,IF(D10="Continuous Lined",K10*20+9,IF(D10="Drops Lined",(K10*20+9)+L10*12,"Not on the list"))))
N10:N17	N10	=CHOOSE(MATCH(D10,{"Continuous Unlined","Drops Unlined","Continuous Lined","Drops Lined"},0),K10*14+8,K10*14+8+L10*6,K10*20+9,K10*20+9+L10*12)
O10:O17	O10	=LET(L,RIGHT(D10,6)=" Lined",D,LEFT(D10,1)="D",K10*(14+6*L)+8+L+L10*6*(1+L)*D)

 

[JoeMo]

Hi JoeMo,
I am probably doing something wrong, but when I copy your formula in, I always get the answer "Not on the List"

Got it Right, Thanks for your help- Perfect solution, Thanks

You are welcome - thanks for the reply.