Calculation based on lists selected
 

Hi,

I have validated a cell(Data Validation) where the validation criteria
allows a list and the source is 2 cells containing Y, N.
Now, depending on the list selected, a formula should be calculated.
ie. if Y, (B1+B2)-(A1+A2)
if N, (C1+C2)-(A1+A2)

I have tried a lot but in vain.

Pls help out.
Sups


---
Message posted from http://www.ExcelForum.com/

Calculation based on lists selected
 

Hi
assuming that your validated cell is A3 and the target cell A4 enter
the following in A4:
=IF(A3="Y",B1+B2-(A1+A2),IF(A3="N",C1+C2-(A1+A2),""))

HTH
Frank

Hi,

I have validated a cell(Data Validation) where the validation
criteria
allows a list and the source is 2 cells containing Y, N.
Now, depending on the list selected, a formula should be calculated.
ie. if Y, (B1+B2)-(A1+A2)
if N, (C1+C2)-(A1+A2)

I have tried a lot but in vain.

Pls help out.
Sups

Calculation based on lists selected
 

Supriya,

Do you just want a formula?

If so, assuming DV is in A1, it is

=IF(A1="Y",(B1+B2)-(A1+A2),(C1+C2)-(A1+A2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Supriya " wrote in message
...
Hi,

I have validated a cell(Data Validation) where the validation criteria
allows a list and the source is 2 cells containing Y, N.
Now, depending on the list selected, a formula should be calculated.
ie. if Y, (B1+B2)-(A1+A2)
if N, (C1+C2)-(A1+A2)

I have tried a lot but in vain.

Pls help out.
Sups


---
Message posted from http://www.ExcelForum.com/

Calculation based on lists selected
 

Hi Frank,

It worked perfectly fine.
Thanks a lot

--
Message posted from http://www.ExcelForum.com

Calculation based on lists selected
 

Frank,

If using DV, then it can only be Y or N, so the extra N test is superfluous.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
assuming that your validated cell is A3 and the target cell A4 enter
the following in A4:
=IF(A3="Y",B1+B2-(A1+A2),IF(A3="N",C1+C2-(A1+A2),""))

HTH
Frank

Hi,

I have validated a cell(Data Validation) where the validation
criteria
allows a list and the source is 2 cells containing Y, N.
Now, depending on the list selected, a formula should be calculated.
ie. if Y, (B1+B2)-(A1+A2)
if N, (C1+C2)-(A1+A2)

I have tried a lot but in vain.

Pls help out.
Sups

Calculation based on lists selected
 

Hi
thanks for the thanks
Frank
Hi Frank,

It worked perfectly fine.
Thanks a lot.

Calculation based on lists selected
 

Bob,
definetly right. Was overcautious :-)
Frank

Bob Phillips wrote:
Frank,

If using DV, then it can only be Y or N, so the extra N test is
superfluous.

Calculation based on lists selected
 

Bob,

You are right!
If there are only only 2 conditions, there is no need to specify th
2nd one.

It simplified my formula

--
Message posted from http://www.ExcelForum.com