I have a workbook with 24 sheets. Each sheet has a parts list F11:K100. On sheet 25, I have a list of all the part numbers used on all of the parts lists. I need to be able to look for every part number on every sheet (column F) and tally the required quantities (column K). Sheet 25 has part numbers (Column A) and total quantities (Column B, where the formula will go). Right now, I'm using VLookUp but the formula is getting to be a bit unwieldy. Is there a cleaner way to basically VLookUp every sheet and tally the results?

1. Your workbook have 24 sheets. Is sheet 25 in a different workbook?

2. ...and tally the required quantities. What is required quantities?Are you trying to compare the data in every sheet of Col.F with Col.K of sheet 25 or something else?

Sheet 25 is in the same workbook. On sheets 1-24, Column F has part numbers and Column K has the quantity required. Sheet 25 is being used to tally total quantities of materials required across the 24 other sheets. Column A on Sheet 25 contains material part numbers, Column B needs to be a total of the required materials across the other 24 sheets. Sheets 1 - 24 are parts lists for products my company manufactures.

For example, if sheet 1 has part number ABC123, quantity 3, sheet 7 ABC123, quantity 7, then sheet 25 should have ABC123 in Column A and 10 in Column B.

Ask Your Question

Weekly Poll