Consolidating multiple sheets into 1

[JessicaV]

Hello Everyone

I have an inventory project I need to make as automated as possible. I have 7 people counting inventory each is given a sheet (MC1-MC7). The counters enter a "code" and a corresponding "weight" for that code. There are multiple codes and each code is assigned a weight. However, I'd like to consolidate all the total weight for each code on another sheet. I used Pivot tables to summarize all 7 sheets and link them directly, but I am afraid that once more codes are added the code positions will change, and it will no longer work.....

I need some formula that does something this: find all codes labeled "01A" on all 7 seven sheets, add them up and plug total sum on any specified cell.

Is there such formula? Please help. I couldn't attach the file, but I could email if you want to see it.

[day92]

Hello Jessica and welcome to the forum.

You can use a variety of formulas such as Sumproduct, and Index to name a couple but I would need to see some sample data to know for sure what you are trying to accomplish. I recommend uploading a dummy workbook with some sample data with expected results.

[JessicaV]

Hi Day92,

I attached a sample file. Please let me know if you can help.

[day92]

I would set up a data tab and then use a sumproduct function. Take a look at the attached spreadsheet and let me know if this is what you are looking for.

[JessicaV]

I would set up a data tab and then use a sumproduct function. Take a look at the attached spreadsheet and let me know if this is what you are looking for.

I will try you method......I used the conditional autosum wizard but it is time consuming as it takes one code at a time......

Also, can you take a look at this file, I just need a simple formula that will simply identify a code from previously copy and pasted data and pick up the weight and average cost automatically instead of doing it manually. I would really appreciate it. Thank you!