Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    IF test: leave cell as is

    I vaguely remember a post asking whether it was possible to do an IF test, give a value/expression if the test was true, but leave the cell as it was if the test was false.

    I can't seem to find it. And I'm not sure how this could be done w/o VBA. But I do have a need for this.

    TIA

    Fred

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fred,

    In B1: =IF(A1>0,"Yes","")

    Now change the value in A1 to something then back to 0/or empty.

    Of course the A1>0 can be replaced by any expression that will evaluate to True/False.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks RG.

    Seems simple but I'm not getting it. What I'm getting is what I'd expect of the IF test. If I start with A1 empty and put your IF test in B1, I get nothing in B1 (ok). If I now put "cat" or "4" in A1, I get "Yes" (ok). If I now set A1 to 0, I get "" (expected but I'm looking to keep the "Yes").

    I think the solution probably involved some VBA probably with a worksheet change event. But I couldn't find it.

    Fred

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fred,

    Ok, here's some VBA you can try it is set to work on any cell in Column A and adjust the same row in Column B.

    Place the following code in the relevant Worksheet Module in your workbook.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A:A"), Target)
       If isect Is Nothing Then
         MsgBox "Ranges do not intersect"   'Comment out after Testing!
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         
         If (Target.Value > 0) Then   '*** Adjust test as appropriate ***
           Target.Offset(0, 1) = "Yes"
         End If
        
         Application.EnableEvents = True '*** Reset Events ***
       End If
       
    End Sub  'Worksheet_Change
    If Column B already contains "YES" it will not be changed if the value in Column A is changed!

    Hope this is what you want.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    To do it with a formula, you need to turn on iterative calculation in Options, then create an intentional circular reference:

    =IF(A1>0,A1,B1)
    in B1 for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    RG, Rory,

    Thanks much.

    This works fine in my little test. Let me try it in my gradebook.

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Rory,

    I'm curious about the iterative solution.

    Using your example of =IF(A1>0,A1,B1) as the formula in B1:
    - B1 has a formula in it as you gave
    - the value of B1, for example, is the word "cat"
    - if A1<=0, the formula evaluates to false, so the contents of B1 becomes whatever had been in B1. Is that the formula or is that the value of B1? Clearly, "cat" is being displayed.
    - suppose A1 is changed to some other negative number so again the formula evaluates to false. But isn't "cat" in B1 so where's the formula?

    While I understand the idea of iterative numerical solutions to converge on a numerical value and stop calculating after x iterations or the change from one iteration to the next is smaller than some threshold, I'm having trouble understanding what's going on here.

    Does turning on iterations somehow tell Excel to allow a cell to be associated with a formula and a "constant"?

    Thanks.

    Fred

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Cells have both a formula and a value (which is why you can open a workbook that has links to other workbooks and choose not to update but have the original values of the formula calculations remain). When you allow iteration, the original value is preserved for the calculation process and used in place of the cell reference.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Rory.

    After I posted my question, I thought about it. Clearly, a cell can have a formula and a value, which is what's usually displayed (I do rarely use CTRL+` to display formulas). But what is it about allowing iterations in Options that makes Excel behave differently than if not allowed (not looking at numerical iterations to converge on a number)?

    As I noted in my original post, someone asked the same question a while ago. It would seem useful to allow the true or false part of an IF test to evaluate to "keep the current value" w/o needing to turn on iterations.

    Just my 2 cents.

    Fred

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I guess having to turn on iterations is a sort of safety measure to avoid accidental circular references (which I see a lot of).
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    New Lounger
    Join Date
    Mar 2011
    Posts
    20
    Thanks
    1
    Thanked 1 Time in 1 Post
    Would a nested if fill the bill, a la:

    =IF(A1=5,"Yes",(IF(A1<>5,,"WTF")))

    It appears to meet all the stated conditions when I try it. I'm not a programmer--I just play one on TV, so glad to defer to those more knowledgeable.

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Fred,

    Perhaps this will do what you want. The code is equivalent to B1=if(A1>0,4,B1). If the expression A1>0 is true, the value of B1 will be stored until the expression A1>0 becomes false in which the original B1 value will be restored. You can change the value of B1 at any time which will then be the value restored when the expression proves false. The value of B1 is permanently stored so it will be available the next time you open the workbook provided that you saved.

    Example: B1=dog. if you enter 3 in A1 then B1 become 4 according to the expression. If you enter 0 in A1 the B1 becomes "dog" again. If you enter "cat" in B1 then "cat" will be the value returned if you toggle the expression.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Worksheets("Hidden")
    If Not Intersect(Target, [a1]) Is Nothing Then
        If .Range("B1") = "" Then .Range("B1") = Range("B1")
        Range("B1") = IIf(Range("A1") > 0, 4, .Range("B1"))
        Application.EnableEvents = True
        Exit Sub
    End If
    If Not Intersect(Target, [b1]) Is Nothing Then
        If .[b1] <> [b1] Then .[b1] = [b1]
    End If
    End With
    Application.EnableEvents = True
    End Sub
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Gumintwork,

    Thanks for the suggestion but that doesn't do what I'm looking for. I entered your formula in B1 and used A1 as you have. When A1=5, then B1="Yes". But when A1 is changed to something other than 5, B1 becomes 0 (the "value" of the "" in the inner IF). I can go back and forth in A1 and this is what will continuously happen.

    See my response to Maud for a fuller explanation of what I'm trying to do.

    Fred

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Maud,

    While I don't know if the thread I was looking for had some of your code, I'll give it a try when I have a little more time (leaving on vacation tomorrow). I'd prefer a formula approach so was trying to implement Rory's suggestion allowing circular reference; while seemingly straightforward, I ran into some problems that I'm trying to work thru.

    Let me be a little more specific about what I'm trying to do.

    This is for my gradebook, for which I've asked lots of questions on this forum.

    Every 2 weeks during the semester (eg, 5 times during a normal fall/spring 15-week semester; we don't go until the end of the semester), instructors have to enter a "monitoring" code that says how each student is doing. "NTR" means "nothing to report," "333" means having trouble with the work, etc. If a code other than NTR is entered, a corresponding letter is generated by "the system" and sent to the student's home address.

    The entry is done on a form containing all the students CURRENTLY in the class. So the first entry, pretty much everyone who started 2 weeks earlier is still there but some may have already switched to another section of the class. Certainly as the semester goes on, students will drop.

    For example, the starting roster has 30 students. By the time of the 1st monitoring period, there might only be 26; 2nd monitoring period only 22;...5th monitoring period only 20.

    What I want to do is save, in my gradebook, the monitoring codes entered for ALL students - even those that have dropped out. Clearly the dropouts will only have 1 or 2 codes in my gradebook, depending on when they dropped out.

    What I want to avoid is having to enter the codes manually again into my gradebook, since I've already done that in the school's form.

    I can download a copy of what I entered as an Excel spreadsheet (about 8 columns for name, ID, ..., and the 5 monitoring entries; 1 row, other than heading rows, per student). But as I suggested above, this spreadsheet will only contain CURRENT students.

    So what I was doing is copying/pasting the school's version of the monitoring spreadsheet (as described in the above para) into my own "monitoring" sheet. My sheet looks like a replica of what I downloaded with the name, 5 cols, etc. The key difference is that mine has ALL the students who were ever in the class whereas the downloaded version (now in my sheet) only has CURRENT students. The school's version gets pasted into the same area of my sheet every 2 weeks, so formulas don't have to change. But as students drop out, the # of "useful" rows I'm pasting decreases (I can paste more rows, but they'll be blank, to make sure there are no left-over students from the previous download).

    If I wait until the end of the semester, I'd have all 5 codes for the students who survived until the end, so I'd only need to do it once for them. But I would have nothing for those who dropped out along the way since they're no longer in the school's version.

    So when I download the school's monitoring sheet every 2 weeks, I want to copy (by formula, not copy-paste) whatever info is in the school's version into my version. Here's where the formula I need comes in.

    Using some general references, hopefully this will be understood (imagine an n-row by 6-col [name and 5 monitoring codes] area in my sheet):
    =if student-on-this-row-in-my-monitoring-area is found in the school's-download, copy the code in the column for that student's row from the school's-download;
    if not found, leave the code I had (maybe from a previous download and they subsequently dropped out)

    I can do the searching with a MATCH - no problem. I can copy a current code - no problem. Where I need help is with the "not found" part. I don't want the IF test to give whatever result it gives when there's no FALSE expression.

    Unfortunately, I don't have the time right now to create a sample since I'm leaving on vacation tomorrow. Hopefully the above will explain what I'm trying to do.

    Thanks for all the help.

    Fred

Posting Permissions

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