Search table for active cells (checked box) and return name

[RPM7]

Hi Everyone, I hope someone can help me as I'm having a lot of trouble with a spreadsheet for work.

I'm trying to create a table which contains all of our machine operators/floor staff and their operations they are certified to do.
Column B4:B22 is the staff names.
Row C3:N3 is all the machines and operations.

An "x" is placed in the cell to identify that person A can operator Machine 1 & machine 5 etc...
Essentially I have created a simple matrix for our production scheduler to identify which machine each person can operate by marking the box with an "x".

I have then created multiple smaller tables representing each of the machines and the hours required on the machine for the weeks production.

Here's where I have the problem, I would like to do a search of the main table down the column of machine 1 (c4:c22) and return all the people that are certified to operate that machine from the names list in b4:b22. (This would be the first small table) The next table would be (d4:d22) & then (e4:e22) etc...

This would allow our production scheduler to see that they need so many hours per week on each machine and they will also know that employee 1 can only do so many hours, therefore they'll need more resources etc..

I hope this makes sense, I can't think of any formula that will work in this instance as the main table is littered with x's and a search for x will only return the first value and ignore the next.

Here's a copy of the file.
https://www.dropbox.com/s/atbrr67c2s241oi/Operator-Machine Hours.xlsx


If anyone can think of a way of making this work, I'd be very grateful.




Thanks.

 

[Andrew Poulsom]

Could you use pivot tables?

Excel 2003
	P	Q
1	Machine 1	x
2
3	Count of Machine 1
4	Employee List	Total
5	Person 1	1
6	Person 13	1
7	Person 14	1
8	Person 6	1
9	Grand Total	4
Sheet2

 

[RPM7]

Hi Andrew,

Thanks for your reply.
I'm not familiar with pivot tables. How do I replicate the table you have placed above?

Can I then reference the total hours from the table or
Can I add up that person 1 has done 10hrs on machine 1, 5hrs on machine 3, and 25hrs on machine 7.

 

[Andrew Poulsom]

For an introduction to pivot tables see:

Create a Pivot Table in Excel 2007

I didn't see any hours in your table of x's.

 

[RPM7]

Hi Andrew,

Sorry for the delay in getting back.

I've been mulling over how this spreadsheet is to operate and I'm not sure if the pivot tables will work for me. They do have many advantages but I'm not quite sure they'll suit this application.
It's probably best that I supply the spreadsheet to give a proper indication of how it should work.

Download link:
https://www.dropbox.com/s/cx9t8zqfby6fjau/Production Schedule.xls

Essentially, the spreadsheet is to be used as a prediction for my companies production line. Its separated into 3 worksheets.

Production: this is the scheduling section and it graphically displays what our target is (threshold), and the required time to produce the products ordered for a particular week.
Essentially we need to match the columns to the threshold line. That's the theory anyway.

Operators: This is a list of employees and the operations they are certified to perform.
It'll also be used to move employees from one operation to another depending on whether they are required.

Data: the amount of minutes required for an operation of each product manufactured.


The section I'm having trouble with is the operators worksheet.
I would like to be able to populate the small tables automatically with the operators name if they are checked off as been certified for a particular operation.

Do you think this is possible?

 

[RPM7]

I finally found what I was looking for.
I needed an Arbitrary Lookup formula. All's working now.

Thanks for having a look at it Andrew.