Solved Call Tracker not auto populating date

January 5, 2017 at 13:18:25
Specs: Windows 7
I made a call tracker where I am just tracking the time in which I get the most calls answered and I want to auto populate the date If i change a value to yes or no between B4:M4 and nothing if false. This is the formula I wrote that is not working:

=IF(OR(B4:M4="Yes",B4:M4="No"),TODAY(),"")

Why wont it work?


See More: Call Tracker not auto populating date

Reply ↓  Report •


✔ Best Answer
January 8, 2017 at 19:24:30
This code will place the current date, as a static entry, in A4 whenever any cell in B4:M4 is changed to Yes or No.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
      If Not Intersect(Target, Range("B4:M4")) Is Nothing Then
         If Target = "Yes" Or Target = "No" Then
            Range("A4") = Date
         End If
      End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 6, 2017 at 03:39:17
I don't have Excel with me right now, so I can't test this, but something like the following should work.

=IF(COUNTIF(B4:M4,"Yes")>0,TODAY(),"")

Obviously, you need to add the OR for the No test.

Basically you need to have Excel check if there are any Yes or No entries in the range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
January 6, 2017 at 09:09:14
=IF(COUNTIF(B4:M4,OR("Yes","No"))>0,TODAY(),"") Is what I tried, still not working as it should. It seems to be throwing back a false result regardless of the changes in B4:M4

Reply ↓  Report •

#3
January 6, 2017 at 12:29:46
You have the OR() in the wrong place, should be something like:

=IF(OR(COUNTIF(B4:M4,"Yes")>0,COUNTIF(B4:M4,"No")>0),TODAY(),"")

Also, I'm not sure you understand how the TODAY() function operates.

The TODAY()function will continually update each time the worksheet is recalculated.
So it will give you the date today as 01/07/2017
and tomorrow, when you open the sheet it will again
give you the date "today" or 01/08/2017

It is NOT a static date.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
January 6, 2017 at 13:46:36
Ah you are correct I was not aware of that. How could I make a static day that pulls from the OS and only changes if the values between B4:M4 change?

Reply ↓  Report •

#5
January 6, 2017 at 16:07:34
You will need a Macro to accomplish this,
unfortunately my macro skills are just above nil,
so hopefully someone else will offer some assistance.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#6
January 8, 2017 at 19:24:30
✔ Best Answer
This code will place the current date, as a static entry, in A4 whenever any cell in B4:M4 is changed to Yes or No.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
      If Not Intersect(Target, Range("B4:M4")) Is Nothing Then
         If Target = "Yes" Or Target = "No" Then
            Range("A4") = Date
         End If
      End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
January 9, 2017 at 07:00:01
DerbyDad this code worked great, now what would be the best approach to have it do the same code in all the following cells. ie Range B5:M5- B1500:M1500 to put the date into N5-N1500. obviously I could manually add the code through that ranges one by one but I'm betting there is a more efficient way of doing this with far less code.

message edited by gimini


Reply ↓  Report •

#8
January 9, 2017 at 08:02:03
This code should handle the range B5:M1500 and place the date in Column N of the row that was changed:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
      If Not Intersect(Target, Range("B4:M1500")) Is Nothing Then
         If Target = "Yes" Or Target = "No" Then
            Range("N" & Target.Row) = Date
         End If
      End If
   End If
End Sub

re: "Also is there a list of all the frequently used predefined terms excel uses?"

I'm not sure what you are asking for here. Excel has a long list of built-in functions, but we are not really using "Excel" in this situation. Excel macros are written in VBA - Visual Basic for Applications, specifically VBA for Excel. Each MS Office "product" (Excel, Word, PowerPoint, etc.) has its own version of VBA with its own list of keywords, methods, properties, syntax, etc.

I'm self-taught in VBA for Excel and still rely heavily on Google to figure out how to do a lot of things. There is a huge amount of code available on the web that simply needs to be modified to fit a given circumstance, such as yours. By reverse engineering code that you find in forums, etc. you can learn a lot.

Many years ago I wrote a tutorial that that includes a number of debugging techniques. These techniques are not just good for fixing code that doesn't work, but they are great for reverse engineering code that you find on the web. Once you understand how a piece of code works, you can usually modify it or at least understand how the method/properties/syntax all play together.

That tutorial can be found here:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#9
January 9, 2017 at 08:28:28
Perfect, I have a little experience with coding from using scripting software but it was not visual basic, I too was self taught with that by reverse engineering code I found. I look forward into advancing my knowledge for this as well. I forgot that ranges could be more than just a column or a row and that they could be both!

Reply ↓  Report •

#10
January 9, 2017 at 08:48:15
Ranges can also be non-contiguous:

Sub ColorRange()
   Range("B4:M15, A1, C2, S2:T5").Interior.ColorIndex = 6
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Ask Question