Home

Help: how to categorise hierarchical data and reveal it to user according to category

Alice Tildsley
Occasional Visitor

Hi all,
I've created a spreadsheet with the company hierarchy. There is a sheet for each department and in each sheet the head is at the top with all their reports underneath, then their reports under them, etc etc. Each person has 4 rows, one for name, one for role, one for specific department and one for location. Each person's name stretches across the amount of people that report to them. The cells of people with more than one report are therefore merged to allow the user to see who is 'above' each person.
I want to develop this spreadsheet to allow the user to find the person they're looking for easily. I want to use key words to identify the contact they need. For example, they're looking for the person in charge of expenses, so they would look in the Finance department sheet and there would be categories, for example Payroll, Expenses, Accounts etc. They would select the category they want, which would reveal the people involved in this section.

Does anyone know a way I can do this? 

I tried adding in a row under each person to write in the category and then group the rows to allow the user to 'reveal' more people, but it doesn't work very efficiently. I want to use some kind of filter or tag or drop box or link function to allow this.

Can anyone help? I'm using Microsoft Excel 2016.

Many thanks :)