| 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
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Perhaps you could change G2980 to:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),1)) and K2980 to =IF(G2980=1, 1, "") One small note on David's formula for G2980. And in K2980 you want =IF(youroriginalG2980formula=0,1,"") Since your formula is evaluating K2980 (which will never be 0 due to the modifications to it), you have to test for =1, not =0. "David Biddulph" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
I don't think this is solvable. I have to have my indirect statement in
G2980 because if there is an actual quantity that is pulled in, I need that number there for an upload. If the cell/worksheet that I'm linking to has no quantity, I still need a number there (1) to provide sort of a "fake" demand in my upload. The Worksheet event code does not work because my upload is almost 65K rows long and I don't know how to change the cell number to correspond correctly within the code. I think that when I "scrub" my upload, anything with a 1 in the K column and a 0 in the corresponding G column will have to manually be changed. My spreadsheet is a nightmare. AHHHHHHHH! "JMB" wrote: Perhaps you could change G2980 to: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),1)) and K2980 to =IF(G2980=1, 1, "") One small note on David's formula for G2980. And in K2980 you want =IF(youroriginalG2980formula=0,1,"") Since your formula is evaluating K2980 (which will never be 0 due to the modifications to it), you have to test for =1, not =0. "David Biddulph" wrote: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
One more try. For G2980:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),IF(INDIRECT(B2740& "!Y13")=0,1,INDIRECT(B2740&"!Y13")),"")) K2980: =IF(G2980=1, 1, "") "Eden397" wrote: I don't think this is solvable. I have to have my indirect statement in G2980 because if there is an actual quantity that is pulled in, I need that number there for an upload. If the cell/worksheet that I'm linking to has no quantity, I still need a number there (1) to provide sort of a "fake" demand in my upload. The Worksheet event code does not work because my upload is almost 65K rows long and I don't know how to change the cell number to correspond correctly within the code. I think that when I "scrub" my upload, anything with a 1 in the K column and a 0 in the corresponding G column will have to manually be changed. My spreadsheet is a nightmare. AHHHHHHHH! "JMB" wrote: Perhaps you could change G2980 to: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),1)) and K2980 to =IF(G2980=1, 1, "") One small note on David's formula for G2980. And in K2980 you want =IF(youroriginalG2980formula=0,1,"") Since your formula is evaluating K2980 (which will never be 0 due to the modifications to it), you have to test for =1, not =0. "David Biddulph" wrote: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
Thank you so much. That totally worked. I was trying to do something like
that but I just couldn't get those last 2 indirects written correctly. You made my day! "JMB" wrote: One more try. For G2980: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),IF(INDIRECT(B2740& "!Y13")=0,1,INDIRECT(B2740&"!Y13")),"")) K2980: =IF(G2980=1, 1, "") "Eden397" wrote: I don't think this is solvable. I have to have my indirect statement in G2980 because if there is an actual quantity that is pulled in, I need that number there for an upload. If the cell/worksheet that I'm linking to has no quantity, I still need a number there (1) to provide sort of a "fake" demand in my upload. The Worksheet event code does not work because my upload is almost 65K rows long and I don't know how to change the cell number to correspond correctly within the code. I think that when I "scrub" my upload, anything with a 1 in the K column and a 0 in the corresponding G column will have to manually be changed. My spreadsheet is a nightmare. AHHHHHHHH! "JMB" wrote: Perhaps you could change G2980 to: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),1)) and K2980 to =IF(G2980=1, 1, "") One small note on David's formula for G2980. And in K2980 you want =IF(youroriginalG2980formula=0,1,"") Since your formula is evaluating K2980 (which will never be 0 due to the modifications to it), you have to test for =1, not =0. "David Biddulph" wrote: 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Redirecting a formula in an "IF THEN" statement
glad to hear it worked
"Eden397" wrote: Thank you so much. That totally worked. I was trying to do something like that but I just couldn't get those last 2 indirects written correctly. You made my day! "JMB" wrote: One more try. For G2980: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),IF(INDIRECT(B2740& "!Y13")=0,1,INDIRECT(B2740&"!Y13")),"")) K2980: =IF(G2980=1, 1, "") "Eden397" wrote: I don't think this is solvable. I have to have my indirect statement in G2980 because if there is an actual quantity that is pulled in, I need that number there for an upload. If the cell/worksheet that I'm linking to has no quantity, I still need a number there (1) to provide sort of a "fake" demand in my upload. The Worksheet event code does not work because my upload is almost 65K rows long and I don't know how to change the cell number to correspond correctly within the code. I think that when I "scrub" my upload, anything with a 1 in the K column and a 0 in the corresponding G column will have to manually be changed. My spreadsheet is a nightmare. AHHHHHHHH! "JMB" wrote: Perhaps you could change G2980 to: =IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRE CT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y 13"),1)) and K2980 to =IF(G2980=1, 1, "") One small note on David's formula for G2980. And in K2980 you want =IF(youroriginalG2980formula=0,1,"") Since your formula is evaluating K2980 (which will never be 0 due to the modifications to it), you have to test for =1, not =0. "David Biddulph" wrote: 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 | |||
Linear Mode
