IF test: leave cell as is

Topic

New Reply

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

Reply | Quote

Replies

Fred,

In B1: [noparse]=IF(A1>0,”Yes”,””)[/noparse]

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

RG, Rory,

Thanks much.

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

Fred

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I guess having to turn on iterations is a sort of safety measure to avoid accidental circular references (which I see a lot of).

Reply | Quote

Would a nested if fill the bill, a la:

=IF(A1=5,”Yes”,(IF(A15,,”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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote