Searching multiple sheets and returning multiple results

[BuzzT]

I have scoured the Internet and found several similar problems, but nothing that helps me with this one. I'm hoping someone can help because I'm really stuck...

I need to search for a number (stored as text to keep the leading zeros in if they are present) in several worksheets and return values from each sheet. For example, if I search for 12345, it might appear on sheets 1 and 3. In that case, I need to return the value of a cell from each sheet. Alternately, if the value appears on only 1 sheet, I still need to have the (single) value returned. Ultimately, the search would return results separated by commas (see sample).

I have included a sample sheet with sample data (far less than will be in the actual sheets). Again, if I was to type 12345, the search would return Column A, Coumn B and (potentially) the sheet name OR I could put a title for each sheet into a specified cell of each sheet.

I have been successfull in finding the first instance of the search result, but have not been able to return all instances of the search. I'm open to formulas or VBA solutions, but really need something soon.

Thanks in advace for any help.