Search and Return a List

Twolf15

New Member
Joined
Mar 7, 2011
Messages
7
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
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

cjcobra

Board Regular
Joined
Apr 9, 2002
Messages
193
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

New Member
Joined
Mar 7, 2011
Messages
7
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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2011
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2011
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2011
Messages
7
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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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))),"")}
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

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