Update a value in an employee record but keep the old value as well

[Ritzl]

Hi all,
I have a nut to crack but do not know how. Hope anybody can help me with a full example. Imagine an employee file: Name, First Name, Rank, Salary etc. What I want to achieve is, if I change e.g. the salary of an employee in the table, that is shows the new salary but in a drop down list or so also the old one and the data until when it was valid. (Imaging the table has 100 employees and not all will be changed at the same data). Any idea how to solve this? Thx in advance to all Excel Gods and Gurus.

 

[cerfani]

I would make a table to serve as a database. In this table you would list all salary assignments... so...

Name, First Name, Rank, Salary, Date Started, Date Ended

Then have your table that you described with each employee and it looksup the salary based on a date input or maybe it just shows most recent salary but your database will keep track of history.

1. table for database... you dont delete or overwrite records, you only add records

2. table to lookup recent salary from database

 

[MickG]

Try this:-
The code add/Update Cell comments i.e. the Salary in the target cell and the date.
NB:- Paste this code in the worksheet Module i.e. (Alt+F11")

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Txt As String
     If Target.comment Is Nothing Then
        Target.Cells.NoteText Date & ":=" & Target.Value
     Else
        Txt = Target.Cells.NoteText
        Target.Cells.NoteText Txt & vbLf & Date & ":=" & Target.Value
     End If
Target.comment.Shape.TextFrame.AutoSize = True
End Sub

 

[Ritzl]

Thx a lot, this would work but the disadvantage would be that I have multiple entries and would have difficulties to count eg. head-count.

 

[Ritzl]

Many thanks, interesting idea, will check it out. Advantage I would not have multiple records for the same employee. Disadvantage I could not pull a report e.g. showing the salary development on a new worksheet when it comes to salary review.

 

[cerfani]

Counting unique entries in a table is not difficult

https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

Regardless, if you want a table that shows a list of previous salary in a dropdown then a table has to exist that tracks these salary assignments. The data validation list needs to reference a range to populate a dropdown.

 

[Ritzl]

Thx, I see what you mean. cheers Marc