Multiple IF statements referencing a data validation list

[shadedlight]

Hello,

In A5 and A6 I have data validation lists.

In C5 and C6 I attempted to write a formulas that would change based on the value choose in A5 or A6

Here are the formulas I have for C5 & C6 but they are not working:

=IF(A5="0%","",IF(A5="20%",0.2*(C2+D2),IF(A5="15%",0.15*(C2+D2),IF(A5="10%",0.1*(C2+D2),IF(A5="5%",0.5*(C2+D2),"")))))

=IF(A6="80%",0.8*(C2+D2),IF(A6="85%",0.85*(C2+D2),IF(A6="90%",0.9*(C2+D2),IF(A6="95%",0.95*(C2+D2),IF(A6="100%",(C2+D2),"")))))

Furthermore when I change:

A6 to 100% I want A5 to change to 0%
A6 to 95% I want A5 to change to 5%
A6 to 90% I want A5 to change to 10%
A6 to 85% I want A5 to change to 15%
A6 to 80% I want A5 to change to 20%

I tried the formula in A6, =IF(A6="95%",A5="5%",""), but that isn't working either.

Ultimately it would be cool if A5 or A6 worked interchangeably. So If I changed A5 or A6 they would change with the same logic as above.

Ex. Change A6 to 95% then A5 changes to 5% or change A5 to %5 then A6 changes to 95%.

Any help is appreciated. Thanks

 

[Chihiro]

Can't have inter-dependency without coding I believe. Since it will create circular reference.

Just set A5 with data validation and obtain value in A6 via calculation.

As for C5 & C6. Just multiply (C2+D2) with respective cells.

See attached.

 

[narunfca]

Please check the enclosed file. Hope this helps.

with best regards

Arun N