Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
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. -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
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. "Pranav Vaidya" wrote: 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. -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
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... -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. "Pranav Vaidya" wrote: 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. -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
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. "Pranav Vaidya" wrote: 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... -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. "Pranav Vaidya" wrote: 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. -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
Anyone?
"Mark C" wrote: 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. "Pranav Vaidya" wrote: 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... -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. "Pranav Vaidya" wrote: 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. -- Pranav Vaidya VBA Developer PN, MH-India "Mark C" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT HELP: Establishing a Training Matrix
Anyine?
"Mark C" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This is kinda urgent. Need help with matrix stuff. | Excel Worksheet Functions | |||
how do i create a training matrix in excel | Excel Discussion (Misc queries) | |||
Re-establishing Links | Excel Discussion (Misc queries) | |||
Re-establishing Links | Links and Linking in Excel | |||
establishing the background colour of a cell | Excel Discussion (Misc queries) |