Excel 101:Data Tracker.

Data Tracker Using if condition



Data Tracker and Monitoring System using Excel.

1. Create 3 Sheet Tab, name it as DataMonitoring-Y17, 2017 and Settings

2. Create and Reserve Column from A-Z

3. In Column B, put this code:
=MAX(IF(E3='2017'!$B$2:$B$11978,'2017'!$A$2:$A$11978)) then press Shift+Ctrl+Enter to have curly bracket. It should be looks like this:
{=MAX(IF(E3='2017'!$B$2:$B$11978,'2017'!$A$2:$A$11978))}

4. In Column C, put this code:
=IF(B3=0,"",IF(N3=1,B3+Settings!C$6, B3+Settings!C$7))
4.1. In Settings Tab, put this identifier.
at Column B, Row 6, put identifier label called: First Priority #1 then at Column C, Row 6, put value such as 7 - this serve as how many days to count (One Week).
at Column B, Row 7, put identifier label called: First Priority #2  then at Column C, Row 7, put value such as 14 - this serve as how many days to count (Two Weeks).

5. In Column E, put this code:
=CONCATENATE(D3," | ",A3)

6. Go to "2017" Sheet
Create and reserve Column A-G

Pages