Locking a generated list after a date

[JazzzyJo]

Hi

I have a column where I generated a list of pay upgrade dates for employes. My formula generate the dates by taking in account what is the status of the employe. That status can change year after years.

I am wondering if there is a way to freeze my list past the "today" date. Meaning that I dont want the date prior to "today" to change. Only the future date on the list to "adapt" depending on the choice done by the employee (the formula generating the list take into account choice made by the user).

Hope I'm clear

Thx

 

[Biz]

Try
Where periods is range name.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCells As Range
    ActiveSheet.Unprotect Password:="Lock"
    '- column for each month
    For Each rCells In Range("Periods").Cells
        If CDate(rCells) <= CDate(Now()) Then
            rCells.Locked = True
        Else
             rCells.Locked = False
        End If
    Next rCells
    
    ActiveSheet.Protect Password:="Lock"
End Sub

 

[sijpie]

I don't know what your table looks like and where the status is held that is used in the formulas. Is the status not tied to a date? (ie each row has a status value)

You could create a macro that runs at startup and changes all calculations refering to paste dates to values. But if you construct your table differently that not all rows refer to only one status value, your problem goes away.

 

[JazzzyJo]

It was my feeling I would have to use VBA. I know nothing about that tho but today is a good day to learn. You guys know a site or a post which have a good tutorial about VBA?

 

[sijpie]

But can't your table be constructed such that the status is held with the date, so that you can do it witha formula

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3						Review
4						Date	01/01/08	01/01/09	01/01/10	01/01/11	01/01/12
5						code	2	3	4	5	6
6	Date	Status	Salary			Status	Salary
7	12/04/08	1	20000			1	20000	20400	20808	21224	21648
8	15/07/09	1	20400			2	24000	24480	24970	25469	25978
9	30/08/10	2	24970			3	28000	28560	29131	29714	30308
10	14/07/11	2	25469			4	32000	32640	33293	33959	34638
11	12/12/11	3	29714
12
Sheet1

 

[JazzzyJo]

It is not my salary column I want to "freeze", it is my Date column. See this post to see what I'm trying to do.

http://www.mrexcel.com/forum/showthread.php?p=2805938#post2805938