IF FALSE

[toc63]

THANKS FOR TAKING THE TIME TO READ MY LETTER.

NOW RIGHT TO THE QUESTION.

WHEN USING AN "IF" FUNCTION.

WHAT I WOULD LIKE TO DO IS GET THE VALUE OF A CELL WHEN TRUE, AND KEEP THE THE LAST VALUE OF THE CELL WHEN IT GOES FALSE.

THE TRUE PART IS EASY, BUT I CAN'T GET THE FALSE PART.

EXAMPLE:

CELL A2 HAS EITHER A VALUE OF 1 OR 0 IN IT DEPENDING ON OTHER ARGUMENTS
CELL A3 IS A VARIABLE BETWEEN 1 AND 120 AND CHANGES DEPENDING ON OTHER ARGUMENTS

CELL A1 IS MY "IF" FUNCTION
IF A2=1
IF TRUE A1=A3 A1 CHANGING AS A3 CHANGES

NOW HERES WHAT I WOULD LIKE
IF FALSE A1=WHAT A3 EQUALED JUST BEFORE A1 WENT FALSE, AND HOLDING THAT VALUE UNTIL A1 GOES TRUE AGAIN

CAN YOU HELP ME????

THANKS

Thomas O'Connor
39 Fairchild dr.
Amherst N.Y. 14226
toc63@earthlink.net

 

[Anne Troy]

What you want to accomplish isn't possible the way you describe.

Why don't you tell us a *real-life* scenario, and we can probably help better.

 

[brettvba]

On 2002-04-29 18:06, toc63 wrote:
THANKS FOR TAKING THE TIME TO READ MY LETTER.

NOW RIGHT TO THE QUESTION.

WHEN USING AN "IF" FUNCTION.

WHAT I WOULD LIKE TO DO IS GET THE VALUE OF A CELL WHEN TRUE, AND KEEP THE THE LAST VALUE OF THE CELL WHEN IT GOES FALSE.

THE TRUE PART IS EASY, BUT I CAN'T GET THE FALSE PART.

EXAMPLE:

CELL A2 HAS EITHER A VALUE OF 1 OR 0 IN IT DEPENDING ON OTHER ARGUMENTS
CELL A3 IS A VARIABLE BETWEEN 1 AND 120 AND CHANGES DEPENDING ON OTHER ARGUMENTS

CELL A1 IS MY "IF" FUNCTION
IF A2=1
IF TRUE A1=A3 A1 CHANGING AS A3 CHANGES

NOW HERES WHAT I WOULD LIKE
IF FALSE A1=WHAT A3 EQUALED JUST BEFORE A1 WENT FALSE, AND HOLDING THAT VALUE UNTIL A1 GOES TRUE AGAIN

CAN YOU HELP ME????

THANKS

Thomas O'Connor
39 Fairchild dr.
Amherst N.Y. 14226
toc63@earthlink.net

you would have to store the original value somewhere else for it to pick it up

 

[Jay Petrulis]

Hi,

Delete the formula in A1. Enter the following code in the sheet module.

'--------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then
If Target.Offset(-1, 0) = 1 Then Target.Offset(-2, 0) = Target
End If
End Sub
'---------

Question: What happens if A2 = 0 and then changes to 1 (true) but A3 doesn't change? In the above, nothing happens. If that needs to be added, let us know.

Bye,
Jay

 

[Schrödinger’s cat]

On 2002-04-29 18:06, toc63 wrote:

NOW HERES WHAT I WOULD LIKE
IF FALSE A1=WHAT A3 EQUALED JUST BEFORE A1 WENT FALSE, AND HOLDING THAT VALUE UNTIL A1 GOES TRUE AGAIN

meow
This message was edited by Schrödinger’s cat on 2002-04-29 20:03

 

[toc63]

JAY

I TRIED YOUR CODE BUT IT ONLY SEEMS TO WORK IF I ENTER THE VALUE BY HAND. I NEED TO BE ABLE TO GET THE VALUE FROM ANOTHER CELL.

THANKS AGAIN
TOM

 

[Jay Petrulis]

Hi Tom,

No need to SHOUT!

What formula is in cell A3? The event trigger can be changed to include the reference cells that comprise the formula, or another way may be found (have to think about that one).

Bye,
Jay

 

[toc63]

Jay

Sorry about the shouting, it's just how I type.

I was hoping to do this without boring you with specifics.

the formulas are as folows:
in cell E7 I have =INDIRECT(E3)
in cell E3 i have =ADDRESS(E1,2,1,1)
in cell E1 I have a spin button inserting a value between 1 and 1000

in cell F7 I have =INDIRECT(F3)
in cell F3 I have =ADDRESS(F1,3,1,1)
in cell F1 I have =E1

referencing our previous messages
A3=F7
A2=E7

columb 2 has variables from 1 to 20
columb 3 has variables from 0 to 1000
The variables are not in any order and can be repeated several times.

Thank You
Tom

 

[Jay Petrulis]

On 2002-05-01 08:01, toc63 wrote:
Jay

Sorry about the shouting, it's just how I type.

I was hoping to do this without boring you with specifics.

the formulas are as folows:
in cell E7 I have =INDIRECT(E3)
in cell E3 i have =ADDRESS(E1,2,1,1)
in cell E1 I have a spin button inserting a value between 1 and 1000

in cell F7 I have =INDIRECT(F3)
in cell F3 I have =ADDRESS(F1,3,1,1)
in cell F1 I have =E1

referencing our previous messages
A3=F7
A2=E7

columb 2 has variables from 1 to 20
columb 3 has variables from 0 to 1000
The variables are not in any order and can be repeated several times.

Thank You
Tom

Hi Tom,

It appears that E1 is the cell that will trigger the change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$1" Then
If Range("E7") = 1 Then Range(???) = Range("F7")
End If
End Sub

Please fix the reference that I am missing (actually correct any that are wrong, of course). I am not following exactly which cell is the original A1.

HTH,
Jay

 

[Asala42]

Hey all,

You could use an intentional circular reference.

Start with setting A2=1 and go to Tools/Options/Calculation and enable Iterations (otherwise you'll get nasty circular reference warnings).

in A1: =IF(A2=1,A3,A1)

That should do it,
Adam