Formula needed to search in multiple sheets for a name, and if yes then sheet title will be listed

Copper Contributor

Hello!  I am not sure if such a formula exists in Excel... but this is what I would like to do...

 

I have an excel spreadsheet of employee training.  There are multiple sheets, all with the exact same table but for each type of training and the list of employees who had the training.

 

On a fresh sheet, Sheet 1, I have made a dropdown list of the employees in a cell, A1. Then in a different cell, B1, I would like a formula that basically says: if A1 (and I choose an employee name from the dropdown list, we'll say John Smith) is found on Sheet 2, Sheet 3, or Sheet 4, then cell A1 (title of the sheet) of the sheets that John Smith is found on will be listed in Sheet 1, B1:B3.  (Like if John Smith is found on all three sheets 2-4, then three training titles would be listed, or if only on sheet 2 and 4, then two training titles are listed).

 

Am I asking too much of Excel?  Thanks!

4 Replies

@downekc09 I think the most straightforward solution to me would be to condense all your tables into one table, and just add a column that indicates which training a given row is for. This would allow you to filter by training to see a list of everyone who has taken that training. It would also allow you to filter by employee to see all the trainings they've taken.

I see mhglb already responded with a nearly identical suggestion but since I typed this earlier but got pulled away, I figured I would still send it:
You could do this by linking tables or by manually entering sheet names in the formula or by having a list of the sheet names, but I suspect you want something a bit more automated to automatically detect all the sheets.
What I would recommend is for you to rethink your data entry in the first place. Consider this:
Master Sheet: A table with columns including Training Name, Employee Name and any other relevant info (or a second table/sheet could have Training Name and relevant info on that training and even a 3rd table/sheet could have Employee Name and their relevant info and yes this is mimicking a database structure).
Report Sheet: This sheet would have a PivotTable (or a custom lookup table to fit a custom form)
You could then also have custom report sheets for training sessions vs employees vs company wide, etc... Having all the data in 1 or a couple linked tables may give you much more flexibility and options.

@downekc09 I ended up figuring something out!  I have a tab with "All" info (I get it online from an account that creates it, then I sort by training).  Then I have separate tabs for each training that automatically update from the "All" tab with the names, expiration dates, if qualified, etc.  Then I have another "Training List" tab that lists each training and hyperlink to that specific training tab.  The last tab I made is the "Employee" tab which is the one I was having trouble with and figured out a couple formulas!  I have a drop down list of each employee name.  When a name is selected, a list of their trainings show up.  I made it so that if "John Smith" is found in this training tab, then the title will be listed.  Then I used conditional formatting to "white" out the "FALSE" results.

Thanks!

The reason I wanted to make it this way was to make it user friendly for some of the employees here. If they want to see a specific employee's training, they use the "Employee" tab, and if they want to see who all has First Aid training, then they use the "Training List" tab and click on First Aid. I also added a Home hyperlink on each tab that goes back to the "Training List" tab.