URGENT HELP: Establishing a Training Matrix

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Just wondering how I wold go about referencing a Job title ID on a master
training matrix to a course grid on another sheet. THe course grid would have
the job title ID and required courses highlighted. So if per say Job 1 was
listed under employee "Jay Smith" in the Master, it would highlight the
required courses or that specific job, referencing from the course grid that
has the required course set up.

Your tiem and help is greatly appreciated.
 
I think this is how is Master worksheet looks like-

Job ID Course
Job 1 course 1
Job 2 course 2
Job 3 course 3
Job 4 course 4
Job 5 course 5

And this is how the other worksheet would look like

Employee Job ID Course
Jay smith Job 3 Course 3

In order to get Course 3 under the column course, enter the below formula-
=VLOOKUP(B2,Master!$A$2:$B$6,2,0)

Also change the range as per your list on Master worksheet.
Hope this helps.
 
Hey Pranav,

The Master worksheet is actually something like this.

EMPLOYEE TITLE ID AI AMPTL ARC etc. (course codes)

Smith, Judy 1 31-May-2007
Adams, Paul 8

And the Course Grid looks like this.

Title ID AI ATT ARC BHOE BH BC
1 Req
2 Req Req

*where all the required are highlighted for the specific Job Title ID

Now how I wanted to approach this was to reference the job title id back to
the course grid meaning if I have 1 then the required courses (i.e. AI) will
be highlighted a colour since they would have to be completed for that job
speficially. SO if someone was a linesman in the office ( they would only
need ATT, ARC, BC for ex.) however someone that is a Billing CO-ordinator
would only need CPR for example.
 
Hi Mark C,

This is going to be a long formula. Here you go-

Title ID AI AMPTL ARC ATT
1 Req Req
2 Req Req Req
Assume the above is your tiltle grid starting from A16

col A col B col C col D col E col F
employee title ID AI AMPTL ARC ATT
Smith 1 Req 0 Req 0
Adams 2 Req Req 0 Req

This one is your emplpoyee grid starting from A2


Now as you can see, as per the title grid, you have the courses listed as
'Req' or '0'.

Below is the formula used in col C for 'AI'
=IF(NOT(ISERROR(INDEX($A$17:$A$18,$B$3))),INDIRECT("B"&INDEX($A$17:$A$18,$B$3)+16),0)

Here change $B$ to $C$ for the col D and so on. Once you have done this, you
can use conditional formatting for highlighting the missing ones...
 
Hey Pranav,

I tried this as it pertains to my excel document and it did not work.
=IF(NOT(ISERROR(INDEX('Course
Grid'!$A$2:$A$26,$B$3))),INDIRECT("B"&INDEX(Master!$A$2:$A26,$B$3)+1),0)

I'm not sure whats wrong but its a big file with around 50 courses and 350
emp. the course grid at A1 with the Title ID and the Master wat A1 with the
EMployee name. I can send you the excel document if you require.
 
Anyone?

Mark C said:
Hey Pranav,

I tried this as it pertains to my excel document and it did not work.
=IF(NOT(ISERROR(INDEX('Course
Grid'!$A$2:$A$26,$B$3))),INDIRECT("B"&INDEX(Master!$A$2:$A26,$B$3)+1),0)

I'm not sure whats wrong but its a big file with around 50 courses and 350
emp. the course grid at A1 with the Title ID and the Master wat A1 with the
EMployee name. I can send you the excel document if you require.
Click to expand...
 
Back
Top