Excel Tracker Help

[CMcCormack1]

Hi All,

We currently have our Knowledge Base tracker on a excel spreadsheet, The Column Titles are: Status, Doc #, Doc Name, Collection, Category, Author, Last Reviewed Date, Review Period (Days), Next Review Date.

Currently we have the LRD + RP to = the NRD.

What i am thinking is if the Status column could change from Reviewed to To Review once it breaches the Next Review Date automatically and unhides the row so we only see the ones that need to be reviewed rather than 200+ rows of data, or puts them in a different sheet on the spreadsheet named to review or something along those lines?

Any help is appreciated.

 

[vladimiratanasiu]

Considering that Next Review date expires today, use this table in a xslm. file and insert the next code. Then, click in any cell of the file and the rows indicated by Reviewed are hidden automatically. P.S. Last review date is picked up in this example for just one item. If are more ones, you need to adjust formula depending on the indicative cells for each one of them.

Review.xlsm
	A	B	C	D	E	F	G	H	I
1	Status	Doc #	Doc Name	Collection	Category	Author	Last Reviewed Date	Review Period (Days)	Next Review Date
2	Reviewed						15/07/2023	1	16/07/2023
3	Reviewed						16/07/2023	2	18/07/2023
4	To review						18/07/2023	3	21/07/2023
5	To review						21/07/2023	4	25/07/2023
6	To review						25/07/2023	5	30/07/2023
Sheet1

Cell Formulas
Range	Formula
A2:A3	A2	=IF(G2<=TODAY(), "Reviewed","To review")
A4:A6	A4	=IF(G4=TODAY(), "Reviewed","To review")
G3:G6	G3	=I2
I2:I6	I2	=G2+H2

Private Sub Worksheet_calculate()

    Application.EnableEvents = False
    Set Rng = Intersect(UsedRange, Range("A:A"))

 If Rng Is Nothing Then GoTo ExitHere

    For Each i In Rng

        If i.Value = "Reviewed" Then
            Rows(i.Row).EntireRow.Hidden = True
        Else
            Rows(i.Row).EntireRow.Hidden = False
        End If
    Next i

ExitHere:

    Application.EnableEvents = True
    Exit Sub

End Sub

 

[vladimiratanasiu]

Hi All,

We currently have our Knowledge Base tracker on a excel spreadsheet, The Column Titles are: Status, Doc #, Doc Name, Collection, Category, Author, Last Reviewed Date, Review Period (Days), Next Review Date.

Currently we have the LRD + RP to = the NRD.

What i am thinking is if the Status column could change from Reviewed to To Review once it breaches the Next Review Date automatically and unhides the row so we only see the ones that need to be reviewed rather than 200+ rows of data, or puts them in a different sheet on the spreadsheet named to review or something along those lines?

Any help is appreciated.

I made a small rectification in formulas, regarding the status vs. period.

Book2.xlsm
	A	B	C	D	E	F	G	H	I
1	Status	Doc #	Doc Name	Collection	Category	Author	Last Reviewed Date	Review Period (Days)	Next Review Date
2	Reviewed						7/15/2023	1	7/16/2023
3	Reviewed						7/16/2023	2	7/18/2023
4	Reviewed						7/18/2023	3	7/21/2023
5	To review						7/21/2023	4	7/25/2023
6	To review						7/25/2023	5	7/30/2023
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=IF(G2<=TODAY(),"Reviewed","To review")
G3:G6	G3	=I2
I2:I6	I2	=G2+H2