If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recalculate the Formula
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(C 10="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 |
#2
|
|||
|
|||
Recalculate the Formula
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(C 10="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 |
#3
|
|||
|
|||
Recalculate the Formula
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! "Tom-S" 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(C 10="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 |
Thread Tools | |
Display Modes | |
|
|