| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Redirecting a formula in an "IF THEN" statement
Hi,
I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Is the contents of G2980 a typed-in value or the result of a formula??
-- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
G2980 is a result of a formula.
"Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Insert the following macro in the worksheet event code area:
Private Sub Worksheet_Calculate() If Range("G2980").Value = 0 Then Range("G2980").Value = 1 Range("K2980") = 1 End If End Sub If G2980 becomes a 0, then both G2980 and K2980 will become 1's -- Gary''s Student - gsnu200757 "Eden397" wrote: G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
I know how to record a macro but do you have time to write out the
instructions for my actions if I wanted to record this into a new macro? This is very interesting to me. If it's too much work, let me know and I will try to perform the other advice below. Thanks, Eden397 "Gary''s Student" wrote: Insert the following macro in the worksheet event code area: Private Sub Worksheet_Calculate() If Range("G2980").Value = 0 Then Range("G2980").Value = 1 Range("K2980") = 1 End If End Sub If G2980 becomes a 0, then both G2980 and K2980 will become 1's -- Gary''s Student - gsnu200757 "Eden397" wrote: G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200757 "Eden397" wrote: I know how to record a macro but do you have time to write out the instructions for my actions if I wanted to record this into a new macro? This is very interesting to me. If it's too much work, let me know and I will try to perform the other advice below. Thanks, Eden397 "Gary''s Student" wrote: Insert the following macro in the worksheet event code area: Private Sub Worksheet_Calculate() If Range("G2980").Value = 0 Then Range("G2980").Value = 1 Range("K2980") = 1 End If End Sub If G2980 becomes a 0, then both G2980 and K2980 will become 1's -- Gary''s Student - gsnu200757 "Eden397" wrote: G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
So what you want in G2980 is =IF(yourformula=0,1,yourformula)
And in K2980 you want =IF(youroriginalG2980formula=0,1,"") -- David Biddulph "Eden397" wrote in message ... G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
What if my Original G2980 formula is:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),"")) ? How do I finish that statement as: =0,1,"" Thanks, Eden 397 "David Biddulph" wrote: So what you want in G2980 is =IF(yourformula=0,1,yourformula) And in K2980 you want =IF(youroriginalG2980formula=0,1,"") -- David Biddulph "Eden397" wrote in message ... G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Where I wrote yourformula, replace that by your formula (without your =
sign). -- David Biddulph "Eden397" wrote in message ... What if my Original G2980 formula is: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),"")) ? How do I finish that statement as: =0,1,"" Thanks, Eden 397 "David Biddulph" wrote: So what you want in G2980 is =IF(yourformula=0,1,yourformula) And in K2980 you want =IF(youroriginalG2980formula=0,1,"") -- David Biddulph "Eden397" wrote in message ... G2980 is a result of a formula. "Gary''s Student" wrote: Is the contents of G2980 a typed-in value or the result of a formula?? -- Gary''s Student - gsnu200757 "Eden397" wrote: Hi, I have a formula in cell K2980 stating "=IF(G2980=0,1,"")" . This works fine but I need to go further with the formula and don't know how. What I want to do is if G2980 =0, then I want a 1 to show up in K2980 but I also want the 0 in G2980 to change to a 1. Is this too circular? Thanks, Eden 397 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| "If" statement returning blank gives downstream formula errors. | Excel Discussion (Misc queries) | |||
| Formula for average with a "IF" statement | Excel Discussion (Misc queries) | |||
| embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Hybrid Mode
