A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Recalculate the Formula



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 11:14 PM posted to microsoft.public.excel.worksheet.functions
malcolm
external usenet poster
 
Posts: 106
Default 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  
Old June 2nd, 2010, 07:18 AM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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  
Old June 2nd, 2010, 01:53 PM posted to microsoft.public.excel.worksheet.functions
malcolm
external usenet poster
 
Posts: 106
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.