Forum Discussion
Need to lookup each employee Trained & Certified data sheets and populate YES or NO
Dear All,
I am posting this from 2016 version. I need formula help for both 2016 version and Office 365 versions.
Problem:
I have 2 tabs ( Trained data & Certified Data of employees) in two sheets. Attached excel sheet for reference.
Help required ? In Need formula sheet
I have employees data ( Emp # and Name in columns A& B in Need formula sheet)
1.Need formula sheet has Course codes in merged cells ( C1 ,F1 etc....) with categories ( Trained, No of times attended and Certified).
2. We want to lookup A3 cell against Trained Data Sheet if he got trained each course against each course which are in Merged cells, If he got trained it should populate Yes under each course with no of times he attended the training ( example : if he has attended multiple times it should reflect)
2. Same way lookup A3 cell with Certified Data Sheet and look if he got certified against each course in Merged cells, If he got certified it should populate Yes under each course
3. If employee has Not trained and not Certified it should reflect NO with Red color Font
For your reference I have updated Manually 2 records for employee KIRAN & Vijay in Need formula sheet.
5 Replies
Vijayvr Please see the attached workbook, where the COUNTIFS and IF functions were used to generate the desired results. Conditional formatting was also used on the entire range to format "No" results with red font and "Yes" with green fill and white font.
Hi Djclements
Good morning
Thanks for sharing the sheet with solution. Still have two queries.
1. Please share the steps to how you applied Conditional formatting.
2. I want to apply filter to see each employee data . so how can i filter and able to see Row 1 & 2 . I tried filtering for employee 234675 but unable to get Row number 2 ( Trained, No of times attended, Certified).Attached sheet for your reference
Vijayvr No worries:
- Start by selecting the entire results range, C3:Z11. On the Ribbon, go to Home > Conditional Formatting > New Rule > Format only cells that contain. Next, under the Format only cells with: section, select "Cell Value > equal to" and type No. Then, click the Format button and specify Bold font, color red. Repeat the same steps to create another rule for cell values equal to Yes with applicable formatting applied. Note: you can view/edit the conditional formatting rules I created by going to Home > Conditional Formatting > Manage Rules.
- If I understood you correctly, to turn on filters so the header row with the filter controls is in row 2, rather than row 1, simply select the entire range starting in row 2 (A2:Z11), then turn on filters (Ctrl+Shift+L). In the attached workbook, I've also moved the EMPID and Name headers to row 2 so they are in line with the other column headers.
I hope that's what you meant. Cheers!
Vijayvr You tagged your post with Powe BI, but perhaps you are willing to use Power Query (PQ) in stead and forget about regular Excel formulas.
The attached file contains a PQ solution with a pivot table. You don't have the Yes/No, but it looks a lot cleaner in my opinion. Should work in all Excel versions after 2013. See it it works for you.
- Thank youRiny
