Display Value in 1 Cell Based on Conditionals from 3 Ranges

**captain_amazing** · 07-20-2011, 09:20 AM

I've attached an example of what I am trying to do.

I am looking to track when employees have taken certain trainings. Sheet1 displays each training with a unique number that represents that training (i.e. A2="1" B2="CPR", A3="2" B3="First Aid").

Sheet2 displays each employee's name with a unique number representing each employee (i.e. A2="1" B2="Jane"). Also in Sheet2 I have each available training displayed in Row 1, so that the sheet displays each employee by row and each training by column.

Sheet3 would be the data entry page. I planned on entering data with the first column being the employee, the second column being the training, and the third column being the date they took the training.

What I am looking to do is display when each employe last took each training at the intersections in Sheet2 (and if they did not take the training to display "0").

I know that this type of information should be entered into a database, but I have been asked specifically to use excel for the job. Any help would be greatly appreciated!

**NBVC** · 07-20-2011, 10:11 AM

In C3 try:

=MAX(IF(Sheet3!$A$7:$A$19=$B3,IF(Sheet3!$B$7:$B$19=C$2,Sheet3!$C$7:$C$19)))

adjust ranges to suit.. but not too big a range... and then confirm with CTRL+SHIFT+ENTER instead of just ENTER and copy down and across.

Format the cells as:

m/d/yyyy;;0;

(you can replace the m/d/yyyy part with your preferred date format).