Results 1 to 14 of 14
Thread: IF test: leave cell as is

20160102, 13:30 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160102, 13:42 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,013
 Thanks
 423
 Thanked 1,608 Times in 1,452 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.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160103, 09:20 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160103, 11:44 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,013
 Thanks
 423
 Thanked 1,608 Times in 1,452 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
Hope this is what you want.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160104, 06:53 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20160104, 08:52 #6
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160105, 09:48 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160105, 10:13 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20160106, 10:07 #9
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160107, 03:03 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20160107, 12:08 #11
 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 programmerI just play one on TV, so glad to defer to those more knowledgeable.

20160107, 19:03 #12
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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

20160108, 10:00 #13
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160108, 10:46 #14
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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 15week 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 leftover 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 copypaste) 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 nrow by 6col [name and 5 monitoring codes] area in my sheet):
=if studentonthisrowinmymonitoringarea is found in the school'sdownload, copy the code in the column for that student's row from the school'sdownload;
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