I am trying create an advanced search function in Excel. I don't know if this is even possible. I have a spreadsheet with 30-40 tabs. On a daily basis, users need to search for account numbers or client names. For any given account/name, each may show up in one tab or multiple tabs.

I have tried using the "find" function in excel, but it is only limited to one tab at a time (I know if you select all tabs, excel will search all, but some of the users of spreadsheet are not savy enough to use this and I don't want to go this route)

There are two ways I am trying: I prefer option 1.

Option 1: create a function similar to what Excel has for searching key words in a document but expand to look at multiple tabs.

Option 2: create a message box that would ask the user "Enter search criteria." Macro would then search and return results in same box. For example if search for word "Smith", macro would display message such as: "Smith found in: tab05, tab12, tab22, tab48".