Value of one cell returns a list from another sheet?

[New2You]

Hi everyone,
I've been trying to figure this out and think I may need to use Arrays somehow, but I am not sure.

Here's the deal:

I have a table of employees as follows: Employee names in the left hand column. Across the top are column headings that are Skillsets they may have (Basket Weaving for example.) The values in each cell in the chart are either H, M, or L for High, Medium, or Low skill levels that the employee has for a particular skillset. On another worksheet named "classes" I want to list the courses that correspond with a skillset and the various skill levels. High skill level Basket Weaving classes might be listed as courses 302, 402, 502 in three separate cells. Medium skill classes may include courses 201, 210. Low skill classes may include 101, 105 and 110.

So lets say Employee A is at skill level "M" for the Basket Weaving in the main table . What I want in a third sheet in the workbook is to list the employees name, the skill, and the recommended HIGH level Basket Weaving classes they could take to increase their skill level from "M" to "H". This data to be used to develop a training plan for the employee.

Not sure if I am making sense...harder to explain that I thought. Figured I would give you guys a shot.

Any thoughts are appreciated. I have not built anything yet - trying to plan it out before I get too deep.

Thanks in advance for any advice!

-New2You

 

[Robert Mika]

How the data in Sheet2 will look like?
With the H,M,L going accross or down the columns?
Can you give a brief layout?

Is this(see below) how your data are being set up:

Sheet1:
Courses

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:121px;"><col style="width:113px;"><col style="width:89px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>employees names</td><td>basket Weaving</td><td>cooking</td><td>Singing</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>mama</td><td>H</td><td>M</td><td>H</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>tata</td><td>M</td><td>H</td><td>L</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>bea</td><td>M</td><td>L</td><td>H</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>rob</td><td>L</td><td>H</td><td>L</td></tr></tbody></table>

Sheet2:

Classses

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:133px;"><col style="width:49px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>basket Weaving</td><td>H</td><td style="text-align:right; ">302</td><td style="text-align:right; ">42</td><td style="text-align:right; ">502</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>basket Weaving</td><td>M</td><td style="text-align:right; ">201</td><td style="text-align:right; ">210</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>basket Weaving</td><td>L</td><td style="text-align:right; ">101</td><td style="text-align:right; ">105</td><td style="text-align:right; ">110</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>cooking</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>cooking</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>cooking</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Singing</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Singing</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>Singing</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Sheet3:

Plan

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:119px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>mama</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>basket Weaving</td><td>cooking</td><td>Singing</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>tata</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>bea</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td>rob</td><td>
</td><td>
</td></tr></tbody></table>
To post your data you can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put a borders around your data in Excel and copy those cells into your post.

 

[New2You]

Thanks for responding Robert!
Unfortunately I am on Office 2011 on a MacBook. Not sure if any of the options you provided to include samples will work as they appear to be windows only options (including Explorer.)

However, looking at your screen shots above - you basically have the structure of what I want to see.

Ideally if a manager was to change somebody's skill level from L to M the list of suggested courses for that employee would dynamically change so that classes that were applicable to "H" skill level were returned.

Hope this makes sense. I'm close to just going through 133 employees manually if I can't figure out how to automate this process.

Any recommendations appreciated!

Best,
New2You