Basic user needs advice on how to create relevant formula / drop down list....

[WeareADP]

Hi,

Normally we have only used Excel for pretty basic stuff, but we are now trying to use it as a Human Resources tool to indicate the people who have particular skills.

I have created a spreadsheet which has names of staff at the top in columns, and then on the left hand side, the rows are filled with a number of skills / training.

Then underneath the staff names, I have been marking with a number 1 if they have that particular skill or not.

We will ideally have 1 sheet such as this per office (we have 7 office locations in total).

Basically, what we would like to have is an overview sheet, which has dropdown function where you can select the skill you want to know about, and then it will bring up a list of all the people who are down to have this skill.

It's a bit complex for me to get my head around but if anyone has any ideas on best methods / formulae which will allow us to create a good spreadsheet for this function, would be much appreciated!

test.jpg

Thank you!

[sourabhg98]

For this first you need to have all the skills mentioned in "Data" sheet to "Overview" sheet. This is because when you will create a dropdown you cannot refer to other sheets in EXCEL 2007 which you are using.
Then you need to extract all employee names based on the value as done in the attached spreadsheet using in array formulas.

Check attached.

NOTE:- Column E and F are helper columns and can be hidden.