Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: IF FALSE

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •