Multiple dates in one cell

Question

I am creating a client tracking form (counseling) and I have a column for Date(s) of Service.  I want the cell for each client to show the date of the last/latest appointment, but to store all other previous appointment dates, so that when I click on the cell, it shows all the previous appointments.  Is this possible to do?

Answer 1

Hi,

I am complete novice to Excel.  It sounds like this would allow me to add the previous dates in a comment that would appear when I click on the cell?  If that's the case, it would work for my purposes.  I see the code (?) you outline, but where/how do I add all that in the spreadsheet?  Thank you!

You will understand that it will not be easy to explain how to integrate this to a complete novice as this requires VBA knowledge.

I have made a little model that you can download here: https://1drv.ms/x/s!AmIR8Z3XdrKRkjXBF5fQJ2eqWCEm

Enter a date in column B, enter a new date in the same cell and the previous date is now added to the comment. 

Let me know if that is what you are looking for, next we can see how to integrate in your model.

Answer 2

Hi,

Sharing an idea how I solved some tracking in a cell.

We had a planning/duty file used by 3 managers and at a certain moment it became a requirement to know who had changed the planning. The more that a manager could overwrite the change.

Therefore I added the changes in a comment.

Sub AddComment(Target As Range, Message As String)

   With Target

      If .Comment Is Nothing Then    ''' target has no comment

         .AddComment

         .Comment.Text Text:=""

         .Comment.Text Text:=Message & PlanningUserInit & Chr(10)

      Else

         If InStr(1, .Comment.Text, Message, vbTextCompare) = 0 Then

               

            .Comment.Text Text:=Message & PlanningUserInit & Chr(10) & .Comment.Text

         End If

      End If

   End With

   

End Sub

This sub was called by the Workbook_SheetChange event.

I am complete novice to Excel.  It sounds like this would allow me to add the previous dates in a comment that would appear when I click on the cell?  If that's the case, it would work for my purposes.  I see the code (?) you outline, but where/how do I add all that in the spreadsheet?  Thank you!

Answer 3

Hi,

Sharing an idea how I solved some tracking in a cell.

We had a planning/duty file used by 3 managers and at a certain moment it became a requirement to know who had changed the planning. The more that a manager could overwrite the change.

Therefore I added the changes in a comment.

Sub AddComment(Target As Range, Message As String)

   With Target

      If .Comment Is Nothing Then    ''' target has no comment

         .AddComment

         .Comment.Text Text:=""

         .Comment.Text Text:=Message & PlanningUserInit & Chr(10)

      Else

         If InStr(1, .Comment.Text, Message, vbTextCompare) = 0 Then

            .Comment.Text Text:=Message & PlanningUserInit & Chr(10) & .Comment.Text

         End If

      End If

   End With

End Sub

This sub was called by the Workbook_SheetChange event.

Answer 4

Not possible.

Perhaps use Access