Forum Discussion
Formula needed to search in multiple sheets for a name, and if yes then sheet title will be listed
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 ...
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.
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.