How to prevent cell value to change after data input?

[lukus2005]

I created a job summary spreadsheet to keep track of project costs and
one of the things it has is a worksheet for every month of the year in
order to keep track of labour costs.

When I enter the timesheets, all I have to do is click on the pulldown
and select the employee and it pulls his hourly rate from an employee
list located off view on that worksheet.

The problem is that the number of employees and the hourly rates on
that list are constantly being changed.

I'd like to be able to update that list without affecting hours that
have already been entered. So if i've entered the hours an employee
has worked up to January 15th and he then gets an increase in salary,
I want to be able to just change his hourly rate in my employee list
without it affect his rate prior to January 15th. Even better would be
if I could maintain only one employee list in a seperate file.

I also want to be able to add or remove employees in that list without
having to adjust my validation formula (the employee list is sorted
alphabetically).

TIA

 

[SmartbizAustralia]

Sounds like the hourly rate could be fixed by using an index / match
formula - forget vlookup as it ain't accurate.
That could get hairy but the formula would look up another sheet where
each time an employee had a rate change, that would be on another
line.

All this is simple with a little vba and alot more robust!

Regards,
Tom Bizannes
Excel Development
Sydney,Australia
http://www.macroview.com.au

 

[Shane Devenshire]

Hi,

If you delete an employee from the list you will loose all your work history
for that employee if you are using formula to do your work.

One solution would be to convert formulas to values up to the current date
and then you can modify the lookup table and the data validation list without
a problem.

You VLOOKUP formulas can look at ranges which are not completely filled -
that means you can leave extra rows for new data.

You can also use dynamic range names to adjust references based on how much
data is in an area of the spreadsheet.

Alternatively, you could run the whole thing via vba but that might be a
major programming job.

 

[Shane Devenshire]

Hi Tom,

Can you give me details on the VLOOKUP inaccuracies, I will file bug reports
with the development team.

 

[lukus2005]

Sounds like the hourly rate could be fixed by using an index / match
formula - forget vlookup as it ain't accurate.
That could get hairy but the formula would look up another sheet where
each time an employee had a rate change, that would be on another
line.

All this is simple with a little vba and alot more robust!

Regards,
Tom Bizannes
Excel Development
Sydney,Australiahttp://www.macroview.com.au

My employee list consists of about 20 employees (2 columns only - Name
and Hourly Rate). I currently make use of the VLOOKUP function and it
has worked fine for me. The only problem is I don't want changes to
the employee list to affect previously entered timesheets.

I think Shane might be on the right track with the conversion of
formulas to values solution.

One solution might be to copy the total salary cost for each employee
to another cell and reset the total to zero... upon saving. Then when
I go back into it the following week to enter the weekly timesheets,
it will only add the new hours and then add it to the previous total.
So changes to the hourly rate won't affect previously saved totals.
The problem with this is how do I tell it to ignore previously entered
hours and executing this upon saving surely requires some vba
programming.

All this might be fairly simple via vba but unfortunately, I have
never touched that part of Excel other than copy existing codes that
didn't require any modifications.

 

[ebloch]

Change the employee's rate table to add columns by effective date of rate
change and then add to lookup to calculate offset by date. Do not leave any
blanks but each column should have rate as of the column's date even if not
changed.

Replace formulas in completed jobs with their values to speed up sheet
recalc and "freeze" the job. ASAP Utilities (free) has a function to do
this.

Eric

Sounds like the hourly rate could be fixed by using an index / match
formula - forget vlookup as it ain't accurate.
That could get hairy but the formula would look up another sheet where
each time an employee had a rate change, that would be on another
line.

All this is simple with a little vba and alot more robust!

Regards,
Tom Bizannes
Excel Development
Sydney,Australiahttp://www.macroview.com.au

My employee list consists of about 20 employees (2 columns only - Name
and Hourly Rate). I currently make use of the VLOOKUP function and it
has worked fine for me. The only problem is I don't want changes to
the employee list to affect previously entered timesheets.

I think Shane might be on the right track with the conversion of
formulas to values solution.

One solution might be to copy the total salary cost for each employee
to another cell and reset the total to zero... upon saving. Then when
I go back into it the following week to enter the weekly timesheets,
it will only add the new hours and then add it to the previous total.
So changes to the hourly rate won't affect previously saved totals.
The problem with this is how do I tell it to ignore previously entered
hours and executing this upon saving surely requires some vba
programming.

All this might be fairly simple via vba but unfortunately, I have
never touched that part of Excel other than copy existing codes that
didn't require any modifications.