Finding which sheet a value is located in,

[splncwm]

Hi All,

I have an Excel workbook that contains multiple sheets with various names and numbers in, the sheets are all an identical lay out like this:

Column A	Column B
Product Name	Serial Number

My question is how could I take a generic list of names and numbers and using a formula work out which sheet they reside in if any, similar to how the find all option of find works?

Unfortunately I am limited to using Excel 2003, and to learn I would appreciate it even if you could just point me in the right direction to solving the formula myself.

Thank you all in advance

 

[Joe4]

Quite frankly, this is a problem that lends itself more to a VBA solution that it does a formula solution, as you really cannot tell a formula to search multiple sheets at once.

I suppose you could string together a bunch of IF statements, but that is going to be long and cumbersome, i.e.
let's say that you have a "Summary Sheet" with data in columns A and B, title on row 1, first row of data on row 2,
and all the other sheets are structured similarly and are named Sheet1, Sheet2, ...
then you would place this formula in cell C2 on your Summary sheet and copy down (shows formula for first 2 Sheets, keeping adding for the other sheets):

=IF(SUMPRODUCT(--(Sheet1!A:A=Summary!A2),--(Sheet1!B:B=Summary!B2))>0,"Sheet1","") &
IF(SUMPRODUCT(--(Sheet2!A:A=Summary!A2),--(Sheet2!B:B=Summary!B2))>0,"Sheet2","") &
...

Note that you can make those formulas a little more efficient if you use exact ranges or named ranges (instead of whole column references like I showed above).