May 25 2021 12:15 PM
May 25 2021 12:15 PM
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!
May 25 2021 01:35 PM
@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.
May 25 2021 02:06 PM
Jun 01 2021 06:52 AM
@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.
Jun 01 2021 06:54 AM