From: Malcolm on 1 Jun 2010 18:14 Earlier today I was given 2 formulas for a problem I was having. I started with a formula that worked and I wanted to add 2 conditions to it. The formula was: =IF(OR('Source Sheet'!C10={“”,”C”, “B”}),'Source Sheet'!B10). I wanted to add a condition that would allow if C10=”S” for any number that I entered in the source sheet B10 to be entered in the destination sheet B10. The second condition would be if “D” was entered in the source sheet a 2 and only 2 would be entered in the destination sheet B10. There is no number in the B10 of the source sheet for “D”, and that is the way it is supposed to be. Here are the formulas I was given, neither worked. Please help. =IF(OR(C10={"","C","B"}),B10,IF(ISNUMBER(B10),IF(C10="S",B10, IF(AND(C10="D",B10=2),2,"")),"")) =IF(OR('Source Sheet'!C10={"","C","B"}),'Source Sheet'!B10,IF(OR('Source Sheet'!C10={"","D","S"}),2,"")) Thanks, Malcolm
From: TomS on 2 Jun 2010 02:18 Assuming all references to C10 are on the Source Sheet, try this: =IF(OR('Source Sheet'!C10={"","C","B"},AND('Source Sheet'!C10="S",ISNUMBER('Source Sheet'!B10))),'Source Sheet'!B10,IF('Source Sheet'!C10="D",2,"")) What it should do is make the cell in which this formula is placed on the Destination Sheet adopt the contents of B10 on the Source Sheet, first of all if any one of these is true: Source Sheet C10="" Source Sheet C10="C" Source Sheet C10="B" Source Sheet C10="S" AND Source Sheet B10 contains a number If none of those conditions are true, it will then test if Source Sheet C10="D", and if it is then the cell in which this formula is placed on the Destination Sheet contains a 2. If none of the above conditions are met then the cell in which this formula is placed on the Destination Sheet contains an empty string (""). Hope this captures the conditions you're trying for. Regards, Tom "Malcolm" wrote: > Earlier today I was given 2 formulas for a problem I was having. I started > with a formula that worked and I wanted to add 2 conditions to it. The > formula was: > > =IF(OR('Source Sheet'!C10={“”,”C”, “B”}),'Source Sheet'!B10). > > I wanted to add a condition that would allow if C10=”S” for any number that > I entered in the source sheet B10 to be entered in the destination sheet B10. > The second condition would be if “D” was entered in the source sheet a 2 and > only 2 would be entered in the destination sheet B10. There is no number in > the B10 of the source sheet for “D”, and that is the way it is supposed to > be. Here are the formulas I was given, neither worked. Please help. > > =IF(OR(C10={"","C","B"}),B10,IF(ISNUMBER(B10),IF(C10="S",B10, > IF(AND(C10="D",B10=2),2,"")),"")) > > =IF(OR('Source Sheet'!C10={"","C","B"}),'Source Sheet'!B10,IF(OR('Source > Sheet'!C10={"","D","S"}),2,"")) > > Thanks, > Malcolm >
From: Malcolm on 2 Jun 2010 08:53 Tom, Hi, Bingo!! You did it! This solution works great. Thank you, Thank you, and Thank you. Best Regards, Malcolm Ps; Did I say Thank you! "TomS" wrote: > Assuming all references to C10 are on the Source Sheet, try this: > > =IF(OR('Source Sheet'!C10={"","C","B"},AND('Source > Sheet'!C10="S",ISNUMBER('Source Sheet'!B10))),'Source Sheet'!B10,IF('Source > Sheet'!C10="D",2,"")) > > What it should do is make the cell in which this formula is placed on the > Destination Sheet adopt the contents of B10 on the Source Sheet, first of all > if any one of these is true: > > Source Sheet C10="" > Source Sheet C10="C" > Source Sheet C10="B" > Source Sheet C10="S" AND Source Sheet B10 contains a number > > If none of those conditions are true, it will then test if Source Sheet > C10="D", and if it is then the cell in which this formula is placed on the > Destination Sheet contains a 2. > > If none of the above conditions are met then the cell in which this formula > is placed on the Destination Sheet contains an empty string (""). > > Hope this captures the conditions you're trying for. > > Regards, > > Tom > > > "Malcolm" wrote: > > > Earlier today I was given 2 formulas for a problem I was having. I started > > with a formula that worked and I wanted to add 2 conditions to it. The > > formula was: > > > > =IF(OR('Source Sheet'!C10={“”,”C”, “B”}),'Source Sheet'!B10). > > > > I wanted to add a condition that would allow if C10=”S” for any number that > > I entered in the source sheet B10 to be entered in the destination sheet B10. > > The second condition would be if “D” was entered in the source sheet a 2 and > > only 2 would be entered in the destination sheet B10. There is no number in > > the B10 of the source sheet for “D”, and that is the way it is supposed to > > be. Here are the formulas I was given, neither worked. Please help. > > > > =IF(OR(C10={"","C","B"}),B10,IF(ISNUMBER(B10),IF(C10="S",B10, > > IF(AND(C10="D",B10=2),2,"")),"")) > > > > =IF(OR('Source Sheet'!C10={"","C","B"}),'Source Sheet'!B10,IF(OR('Source > > Sheet'!C10={"","D","S"}),2,"")) > > > > Thanks, > > Malcolm > >

Pages: 1 Prev: Calculate the number of seconds between two times Next: Locking A Sheet Using Words & Numbers 