Value of one cell returns a list from another sheet?

New2You

New Member
Joined
Sep 27, 2011
Messages
8
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

New Member
Joined
Sep 27, 2011
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top