Prev: Calculate the number of seconds between two times
Next: Locking A Sheet Using Words & Numbers
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: Tom-S 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!

"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(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