I have 21 sheets that are all identical filled with part #'s and data to go with those part numbers. On each sheet there are 4 shifts. For each shift, including the part number there are 9 cells side by side. I would like on another sheet to set up some kind of lookup, that when I type in a partnumber in cell A1, below that it will sit out all the times that the part number has been run regardless of which sheet it is located on. Is there a way I can do this?

Attach a sample of the workbook here as a zip file.

I have three sheets on this workbook. The first two are where all the data go (there are 21 total that have data that go in them). The last sheet, is where I would like all the data to go. On the top of all the sheets is the name of what is in that column of cells (I know you know this already), The 8 cells that are right of the part# are what I would like to go into the total sheet. If the part number goes too that is fine. A part number will never show up on both sheets on the same day, it may however show up on two sheets but different days, they will never overlap with each other. The date that the number ran doesn't matter either. Pretty much I would like...say part number...2002042. If I type this in cell A1 of the total sheet....that below it, it will search all the sheets and copy everything from whatever sheet it ran on into the same place on the total sheet. All the sheets are the same, set up the same and everything.

If you need any more information just let me know but the above should do the job for you...I hope.

thanks

Since the part numbers can appear in 21 worksheets (WOW!), creating multiple VLOOKUP function in the Total worksheet is unreasonable. It would make the workbook a HUGE file and slow because of the formulas.

I would suggest rather to revise the template into 1 worksheet wherein it has all the information for the data. Add another column after the D-shift that says "Press #" and "Shift" so you could use a PIVOT TABLE since, again, part numbers would be appearing multiple times.