Filter List Based On Certain Criteria

Hi

I have a worksheet which is used to monitor the hours worked by certain members of staff. The staff are listed in column A and their hours are marked with a '1' under the header of each hour. Each member of staff is skilled in certain areas of work and their skill set is listed on a separate sheet.

Cell B3 has a list of each skill set and I am looking the filter the members of staff based on the whether they are able to complete the selected work type. This will allow me to monitor whether the work type is being covered by at least 1 member of staff across the whole day.

I will probably have to insert a code into the sheet in order for this work however I am unsure whether this is the correct action or where to start with this so any help would be appreciated.

Attached is a test sheet as an example

Test Sheet - Rota.xls

Thanks

Scott

Hi DILIPandey

Thanks for your reply,

Yes you are right, the filter is in B2 and not B3. My mistake!

I would like the 1's to remain under the relevant hours for the specific members of staff, but when I select the work type I would like to hide the rows which contain staff members who are not skilled in this area of work. Once the selected work type is deleted out of cell B2, and this returns to a blank cell, I would like every member of staff to be listed as before.

I have attached an amended worksheet which displays those skilled under telephone and how I would like the sheet to be displayed. David, Daniel and Steven are hidden as they are not skilled in this work type however, once telephone is deleted from cell B2, I would like their names to reappear on the list.

The skills on this worksheet differ slightly from the original attachment in order to filter staff.

Test Sheet - Rota (Telephone).xls

I hope my explanation makes sense and helps!

Thanks

Scott

Hi DILIPandey

Thanks for your help, the sheet works with the dropdown however is it possible to display every member of staff when cell B2 is blank?

The purpose of the sheet is to see the hours each staff member is working however if we require to view which staff members are available under a certain skill then the full list can be filtered based on the information from the skill tab

Thanks

Scott

Hi DILIPandey

The idea behind the Rota sheet is we have a record of when each member of staff is available to work. This should be the primary view of the sheet and should be available to view when cell B2 does not have any contents. In the event that we want to see which members of staff are available to carry out cetain tasks, I would like to be able to select the work type at the top and for the names of the staff members to be filtered based on whether they are skilled in the selected work type. This will also filter the hours being worked by these members of staff. Once the content of B2 is deleted, the sheet returns to a full list of staff members and their working hours.

Thanks

Scott

Hi DILIPandey

The filter works great however is it possible for the formulas to be added to the cells which contain the staff names as oppose to the cells which display the hours being worked?

The reason I ask this is that a formula will be put in place to look up the hours being worked based on the information available on a seperate sheet. As the hours will be updated based on the information in column A, I would ideally like to leave these formulas untouched. I apologise that this formula is not on the test sheet however as I was looking to filter the names I did not insert it.

Instead, can the formula be added to column A with the names feeding off the staff list on the List tab.

Sorry for any inconvenience

Thanks

Scott

Hi DILIPandey

Sorry to confuse matters...

I would like a sheet where no formulas need to be added to the cells which contain the hours being worked and if B2 and any of the contents of A6:A11 match the data on the skill tab then the rows containing the matching name remain visable and those rows which do not match become hidden.

Unfortunately, as different formulas need to be entered into the cells which contain the hours being worked, I cannot use the current format

Thanks

Scott