Redirecting a formula in an "IF THEN" statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
So what you want in G2980 is =IF(yourformula=0,1,yourformula)
And in K2980 you want =IF(youroriginalG2980formula=0,1,"")
 
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
 
What if my Original G2980 formula is:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRECT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y13"),"")) ?

How do I finish that statement as: =0,1,""

Thanks, Eden 397
 
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
 
Perhaps you could change G2980 to:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRECT(B2740&"!D11")={"Quota",222}),INDIRECT(B2740&"!Y13"),1))

and K2980 to
=IF(G2980=1, 1, "")


One small note on David's formula for G2980.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.
 
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!
 
One more try. For G2980:
=IF(INDIRECT(B2740&"!d11")="ALL",9999,IF(OR(INDIRECT(B2740&"!D11")={"Quota",222}),IF(INDIRECT(B2740&"!Y13")=0,1,INDIRECT(B2740&"!Y13")),""))

K2980:
=IF(G2980=1, 1, "")
 
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!
 
glad to hear it worked

Eden397 said:
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!
Click to expand...
 
Back
Top