Hi Guys,
Good day to you!
I am trying to make a training plan and record in one workbook.
In training plan i have to put numbers, which represent for key training description and the other sheet - the training record i have to input the actual dates of training they undergone.
So, when dates will be inputted on the training record then the training plan sheet, the certain training cell will change the green color that it means he is already trained but the data on the training plan must not be changed.
Below is the example:
Training Plan Dummy First Sheet
Sl. No. Names January February March April May June July August
1 Ann 1
2 Sebastian 2
3 Lhuan 4
5 Peter 5
Key
1 Customer Care
2 QA Operating Procedures
3 Minutes of Meeting
4 Professional Business Correspondence
5 Company Induction
6 Arts of Public Speaking
Training Record Second Sheet
Sl. No. Name Designation Customer Care QA Operating Procedures Minutes of Meeting
1 Ann Admin Asst. 1 Jan 2010
2 Sebastian Supervisor
3 Lhuan Admin Officer. 1 Jan 2010
4 Peter Manager
For the above example, in Training Plan the No. 1 for Ann's cell will automatically become green, as she finished her training based on Training Record sheet and for Sebastian and the rest cells stays red color because the date of their training are not yet done and once the training record will be updated on the date of his training then the training plan (certain cell) will automatically update to green color.
I have a coding below:
Green =INDEX(TbleLookup,MATCH(INDEX($B$2:$B$18,ROW(E2:E18)-1),Name,0),MATCH(VLOOKUP(E2,$A$20:$D$87,2,FALSE),TrainingType,0))<>""
Red =NOT(INDEX(TbleLookup,MATCH(INDEX($B$2:$B$18,ROW(E2:E18)-1),Name,0),MATCH(VLOOKUP(E2,$A$20:$D$87,2,FALSE),TrainingType,0))<>"")
However, it will not read the whole table. Only 2 rows it will look up but i need to execute 7 rows but it is not working well... can you please check where I made a mistake.
Hope it is clear now and you can help me to sort out this matter.
Thank you.
Regards,
Cherry