Search and Return a List

[Twolf15]

I have a excel spreadsheet that looks something like this:
Name.....Stuff 1..... Stuff 2..... Stuff 3
Dude 1 .....X.............................X
Dude 2 .....................X
Dude 3 .....X..............X.............X
Dude 4 ....................................X

So I have sheets for Stuff 1, Stuff 2 and Stuff 3

I need the sheets for stuff to list who does NOT have an X in the
column for each stuff

So the sheet for Stuff 1 should list Dude 2 and Dude 4. Stuff 2 should
list Dude 1 and Dude 4. Stuff 3 should list Dude 2. Get it?

My actual workbook has 150 names on it, but I think that my example shows what I need it to do.

I thought VLOOKUP would do the trick, but apparently now.

Any suggestions?

-Twolf

 

[cjcobra]

If all the sheets are set up alike, use =IF(A2="x",A1,""). Copy this across all sheets. It will pull the Dude's name.

 

[Twolf15]

cjcobra,
That works to the point of it gets me their names, but now theres a crap load of blank cells. Is there a way to get rid of those blank cells?

 

[Haseeb Avarakkan]

Hello, Welcome to the board!

What version of excel are you using Excel 2007+ or Excel 2003-?

Is it possible to enter each sheet names, like Stuff 1, Stuff 2.... on A1 or any other cell in the actual sheets?

This can avoid a volotile function.

 

[Twolf15]

Haseeb,
Thanks for your reply!

I'm using Excel 2007.

Basically this is a workbook that is trying to track the training of employees. What I want, is one master list with all employees, and a column for each training task "Stuff" in my example. If they have completed the task, they get an "X", if they have not completed it, the cell remains blank. Then, there needs to be a sheet for each task ("Stuff" sheets), that auto populates a list of employees who have NOT completed that task.

Since VLOOKUP only works left to right, I have the employees names in the first and last columns (The column to the right simply copys the column to the left).

Hope this helps you figure out what I'm trying to do.

-Twolf

 

[Haseeb Avarakkan]

Can you make a drop down list of your training, like Stuff 1, Stuff 2.....? You already have the names on master sheet. just a create a range name & use it as validation list. So when ever you select the training name from the list, you can see the employes are not completed the training associated the training you selected. This will avoid to make seperate sheet for each training.

Is it Ok?

 

[Twolf15]

As long as I could print the sheets to hand out ot supervisors to show them who has not completed the task.

Would the master list still work with placing an "X" in the column for each task?

 

[Haseeb Avarakkan]

Hope Sheet1 contains your data,

In new Sheet, create drop down list of your training, like Stuff 1, Stuff 2,..... in A1, Then enter,

A3, Ctrl+Shift+Enter, not just Enter, copy down...

=IFERROR(INDEX(Sheet1!$A$2:$A$160,SMALL(IF(1-(Sheet1!$A$2:$A$160=""),IF(1-(INDEX(Sheet1!$B$2:$D$160,0,MATCH($A$1,Sheet1!$B$1:$D$1,0))="X"),ROW(Sheet1!$A$2:$A$160)-ROW(Sheet1!$A$2)+1)),ROWS(A$3:A3))),"")

Change the red highlighted where your validation list located.
Change the green highlighted with your data width.

You can see the employees are not completed training you selected in A1.

 

[Twolf15]

Haseeb,
I tried what you suggested, and it will only return the first employee on the list. Can't figure out what I did wrong....

 

[Haseeb Avarakkan]

Select the first cell contains the formula. Press F2, then press Control+Shift+Enter, not just Enter. Then copy down...

If you did successfully, your formula will be like this,

{=IFERROR(INDEX(Sheet1!$A$2:$A$160,SMALL(IF(1-(Sheet1!$A$2:$A$160=""),IF(1-(INDEX(Sheet1!$B$2:$D$160,0,MATCH($A$1,Sheet1!$B$1:$D$1,0))="X"),ROW(Sheet1!$A$2:$A$160)-ROW(Sheet1!$A$2)+1)),ROWS(A$3:A3))),"")}