Searching workbook by staff member name

Hi all,

I have a portfolio manager document that I have created (attached HERE).

It currently has an automated index and search box where you can search that index of worksheet titles.

I would like to have an additional search box where you can search by the staff member assigned to the worksheet, ie you type in their name and an indexed list filters to the projects that they are leading. The staff member's name exists in cell F4 of each worksheet.

Can anyone help me with a way to search by staff member in F4?

Many thanks,

Matt

Hi Matt,

Since I live in Colac, I have an obvious interest, so happy to help.

I am not sure if this will help, but in Col B you could have the formula =INDIRECT("'"&A2&"'!F4")

This will give you the name beside each project. Then Filter on Col B, and you will get the list the way you want it.

All the best,

David

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,

Nice to meet one of our stakeholders!

That formula has worked perfectly, I appreciate your help.

Now I have that, I have been able to add form control with option buttons to allow the search box to search by either the index column or the comms lead column.

A great result!

Many thanks,

Matt

Hi Matt,

Pleased to hear my idea helped.

I had a play with your model this afternoon, and added a couple of techniques I found that you might find interesting.

In Column B I entered the formula I suggested.

In column K there is an ARRAY formula =INDEX(Leader,MATCH(0,COUNTIF($K$1:K1,Leader))) - this provides a UNIQUE list of names.
Leader is now a dynamic named range.

I have added a COMBO BOX for the unique names which is linked to another dynamic named range called LeaderU. The beauty of the Combo Box is that you can type, and it will confirm/predict the name you want.

I hope this helps, but I gather you are well on the way anyway.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".