Weird formula request

M

magmike

I would like to have a column that records the date that another
column ("Status") was populated with a value.

The column titled "Status," when populated with a value, marks the
row
a certain color depending on the text entered in the field. For
example, if I enter "cb" (meaning 'call back') the row turns a light
yellow. There are about 10 different possibilities that could be
entered and colors that correspond with the entered text.


Regardless of the text entered into the "Status" column, I would like
another column's field in the same row to be automatically populated
with that day's date when the "Status" column is first populated
(regardless of the entered text) AND I do not want that date to ever
change, even if the text of the "Status" column is changed or
deleted.


Is this possible?


Thanks in advance for your help!


magmike
 
G

Gord

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then '1 is column A
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.


Gord Dibben Microsoft Excel MVP
 
M

magmike

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then  '1 is column A
        n = Target.Row
        If Excel.Range("A" & n).Value <> "" _
              And Excel.Range("B" & n).Value = "" Then
        Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.

Gord Dibben    Microsoft Excel MVP








- Show quoted text -
Click to expand...

Thanks. That works great!
 
M

magmike

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then  '1 is column A
        n = Target.Row
        If Excel.Range("A" & n).Value <> "" _
              And Excel.Range("B" & n).Value = "" Then
        Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.

Gord Dibben    Microsoft Excel MVP








- Show quoted text -
Click to expand...

PS: Can I have multiple versions of this code on the same sheet? I
would also like to have another date column to record when a specific
word is typed into the Status column. For example, I modified it for a
second date column to record the date that the word "quote" was
entered into column 5. However, either I modified it wrong, or this
code only works once? Here is how it appears in my code sheet (with
both codes present):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value <> "" _
And Excel.Range("M" & n).Value = "" Then
Excel.Range("M" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_ChangeQTE(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value = "quote" _
And Excel.Range("N" & n).Value = "" Then
Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
 
G

GS

magmike has brought this to us :
PS: Can I have multiple versions of this code on the same sheet? I
would also like to have another date column to record when a specific
word is typed into the Status column. For example, I modified it for a
second date column to record the date that the word "quote" was
entered into column 5. However, either I modified it wrong, or this
code only works once? Here is how it appears in my code sheet (with
both codes present):
Click to expand...

Yes! Just change the structure of the inner If...Then:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value <> "" _
And Excel.Range("M" & n).Value = "" Then
Excel.Range("M" & n).Value = Format(Date, "mm-dd-yyyy")
Click to expand...

ElseIf Excel.Range("E" & n).Value = "quote" _
And Excel.Range("N" & n).Value = "" Then
Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")
 
M

magmike

magmike has brought this to us :







Yes! Just change the structure of the inner If...Then:


      ElseIf Excel.Range("E" & n).Value = "quote" _
             And Excel.Range("N" & n).Value = "" Then
               Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")


--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Fantastic - this is great! One last thing...

By doing this, I notice that if my first entry into the "E" column is
"quote", then it puts the date into column M, but NOT column N at the
same time. I also created a third column (Column O) for the "sent"
code (entered in column E) and the same thing. If I were to start by
inserting "cb", and then rewriting column E with "quote" and then
rewriting column E with "Sent" - it all works perfect - however, I may
end up speaking with a prospect and sending the quote right then and
there while I am on the phone. in that situation i would START by
typing "Sent" into the E Column and would want both columns N and O to
populate with the date.

How do I do that?

Thanks,
 
M

magmike

Fantastic - this is great! One last thing...

By doing this, I notice that if my first entry into the "E" column is
"quote", then it puts the date into column M, but NOT column N at the
same time. I also created a third column (Column O) for the "sent"
code (entered in column E) and the same thing. If I were to start by
inserting "cb", and then rewriting column E with "quote" and then
rewriting column E with "Sent" - it all works perfect - however, I may
end up speaking with a prospect and sending the quote right then and
there while I am on the phone. in that situation i would START by
typing "Sent" into the E Column and would want both columns N and O to
populate with the date.

How do I do that?

Thanks,- Hide quoted text -

- Show quoted text -
Click to expand...

Also, is there a way to ignore case? This method seems case sensitive
 
G

GS

Try this...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long, s As String
On Error GoTo enditall
If Target.Column = 5 Then '1 is column A
Application.EnableEvents = False
n = Target.Row: s = UCase$(Target)
Select Case s
Case "QUOTE", Range("M" & n) = ""
Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "SENT", Range("N" & n) = ""
Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
M

magmike

Try this...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)
    Select Case s
      Case "QUOTE", Range("M" & n) = ""
       Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("N" & n) = ""
       Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

I have tried to modify this to fit my situation and have not had much
luck. I'm not sure where I have gone wrong, but here is my attempt:
NOTE: I need Column M to populate with a date regardless of what I
type in the first time, and if that initial text corresponds with
another one of the Case instructions below, that column should be
populated as well.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long, s As String
On Error GoTo enditall
If Target.Column = 5 Then '1 is column A
Application.EnableEvents = False
n = Target.Row: s = UCase$(Target)
Select Case s
'Case "", Range("M" & n) = ""
'Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "in", Range("N" & n) = ""
Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "quote", Range("O" & n) = ""
Range("O" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "sent", Range("P" & n) = ""
Range("P" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "req", Range("Q" & n) = ""
Range("Q" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "done", Range("R" & n) = ""
Range("R" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
G

GS

Mike,
You did state that you wanted the entries to NOT be 'case sensitive',
and so if you look at how the string variable 's' is loaded it contains
the 'Target.Value' in UPPERCASE. This means your Select Case checks
MUST ALSO be UPPERCASE.

Here's revised code that I think will do what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long, s As String
On Error GoTo enditall
If Target.Column = 5 Then '1 is column A
Application.EnableEvents = False
n = Target.Row: s = UCase$(Target)
Range("M" & n) = Format(Date, "mm-dd-yyyy")
Select Case s
Case "IN", Range("N" & n) = ""
Range("N" & n) = Format(Date, "mm-dd-yyyy")

Case "QUOTE", Range("O" & n) = ""
Range("O" & n) = Format(Date, "mm-dd-yyyy")

Case "SENT", Range("P" & n) = ""
Range("P" & n) = Format(Date, "mm-dd-yyyy")

Case "REQ", Range("Q" & n) = ""
Range("Q" & n) = Format(Date, "mm-dd-yyyy")

Case "DONE", Range("R" & n) = ""
Range("R" & n) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
M

magmike

Mike,
You did state that you wanted the entries to NOT be 'case sensitive',
and so if you look at how the string variable 's' is loaded it contains
the 'Target.Value' in UPPERCASE. This means your Select Case checks
MUST ALSO be UPPERCASE.

Here's revised code that I think will do what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)
    Range("M" & n) = Format(Date, "mm-dd-yyyy")
    Select Case s
      Case "IN", Range("N" & n) = ""
       Range("N" & n) = Format(Date, "mm-dd-yyyy")

      Case "QUOTE", Range("O" & n) = ""
       Range("O" & n) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "REQ", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

      Case "DONE", Range("R" & n) = ""
       Range("R" & n) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Perfect!
 
M

magmike

magmike formulated the question :







Great! ..glad to help.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Got a wierd issue with this - it works - however, when I come in the
next day, it doesn't work anymore, but the code is still there and
correct. To get it to work again, I have to delete the code, save the
workbook, close it, reopen, repaste in the code and then it will work
again.

Any ideas?

PS: Was this one supposed to be under an Option Explicit?
magmike
 
G

Gord

Sounds like macros are being disabled when the workbook is opened.

Check your Trust Center settings.

Best practices require all code to under Option Explicit.

Only way to effectively trouble-shoot faulty code.


Gord
 
M

magmike

  He could be missing the prompt right at the top of the page.

  Select 'enable'.  :)








- Show quoted text -
Click to expand...

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike
 
M

magmike

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike- Hide quoted text -

- Show quoted text -
Click to expand...

PS: I want the date to remain in column M, even if column E is deleted
later.
 
C

Clif McIrvin

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike- Hide quoted text -

- Show quoted text -
Click to expand...

PS: I want the date to remain in column M, even if column E is deleted
later.


--------

I added a test to Garry's code to see if there is already something in
column M -- if column M is not empty (BLANK) it is not modified.

I think this is the latest edition of Garry's code .... you need to
verify that .... or, just copy and paste the section that I changed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long, s As String
On Error GoTo enditall
If Target.Column = 5 Then '1 is column A
Application.EnableEvents = False
n = Target.Row: s = UCase$(Target)

With Range("M" & n)
If IsEmpty(.Value) Then
.Value = Format(Date, "mm-dd-yyyy")
End If
End With

Select Case s
Case "IN", Range("N" & n) = ""
Range("N" & n) = Format(Date, "mm-dd-yyyy")

Case "QUOTE", Range("O" & n) = ""
Range("O" & n) = Format(Date, "mm-dd-yyyy")

Case "SENT", Range("P" & n) = ""
Range("P" & n) = Format(Date, "mm-dd-yyyy")

Case "REQ", Range("Q" & n) = ""
Range("Q" & n) = Format(Date, "mm-dd-yyyy")

Case "DONE", Range("R" & n) = ""
Range("R" & n) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
M

magmike

PS: I want the date to remain in column M, even if column E is deleted
later.

--------

I added a test to Garry's code to see if there is already something in
column M -- if column M is not empty (BLANK) it is not modified.

I think this is the latest edition of Garry's code .... you need to
verify that .... or, just copy and paste the section that I changed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)

    With Range("M" & n)
      If IsEmpty(.Value) Then
         .Value = Format(Date, "mm-dd-yyyy")
      End If
    End With

    Select Case s
      Case "IN", Range("N" & n) = ""
       Range("N" & n) = Format(Date, "mm-dd-yyyy")

      Case "QUOTE", Range("O" & n) = ""
       Range("O" & n) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "REQ", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

      Case "DONE", Range("R" & n) = ""
       Range("R" & n) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :)- Hide quoted text -

- Show quoted text -
Click to expand...


Perfect!

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wierd Formula Request 2
Autonumber field in Excel that behaves the same as Access 0
Link a text box based on drop down box with four options MS Word 365 0
Help? I need help in making formulas work to accomplish... 0
help on a formula (match and etc..) 1
Insert Date into Field2, the first Time Field1 is Populated with Dataon Form 2
Cumulative formula for word table 0
Date Field Subtraction 3

Top