I have a workbook that has 26 sheets. Sheet 1 is a master inventory list. Sheets 2-26 are subinventories with items from the master list. I need to formulate the master list to populate master quantities from the subinventory quantities from items with corresponding identifiers.

Ex master list
Item#|name|authorized qty|on hand qty

I need to find all instances of item# a and add all of its on hand quantities to the master on hand quantity.

Pivot table? Filters? Access with a query? Sorry, not really an expert, but seems like you might be overthinking it, or I'm not getting it.

26 sheets... wow. I work for the Government, and 26 sheets still amazes me.

If you just need simple figures, you can reference cells on other sheets easily.

Just use the Sheet Name and an !  then the cell.

Like   Sheet4!A5

Sounds like a job for sumif() or countif()

I don't know anyway to do it that isn't really ugly or rather hard.

If all subinventory sheets contain all items and are in the same order you could simple sum the quantities from each sheet =sheet1!B1+sheet2!B1+sheet3!b1.......
If all subinventory sheets contain all item numbers but they are in a different order then you could string together 25 vlookups.=vlookup+vlookup+vlookup...... If all item number aren't on each sheet this would become completely unworkable as you'd need to correct the #N/A to 0 --- if(isna(vlookup),0,vlookup)
You could merge all 25 subinventory sheets into one sheet and then aggregate your quantities with a pivot table. The use a vlookup off the pivot table to populate your master inventory.

It would be better in Access. Create a new database. Link all 26 worksheets to the database. Create a query joining all 25 tables to the master table on the item number(include all records form the master table and only records from the other 26 where the item numbers are equal). Output all the fields from your master table plus the sum of quality on the 25 sub inventories.

If your feeling really froggy and insist on doing it in excel it would be possible write a neat little vba  custom function to do it. you would have to loop through all 25 sub inventories do a vlookup sum the results and return the total back to the cell. that would be the cleanest but you need to program.

I'd just do it in access.

I should ask is this something you have to maintain regularly I'm guessing it is.

It you have to maintain it learn enough access to get it done.
If you only have to do it once and never again. Copy all your subinventories to on sheet and do the pivot table.

QFT

This will definitely be a living document.

it is the last situation, where all the items are on the master and only some items are in the sub sheets.

Been doing VBA automation for years. Is that something you can handle? Seems like a good solution for a living spreadsheet, especially if your data sets are dynamic.

You definatly need to learn enough Access to do this. You can leave to excel workshop as it is.

Link all the worksheets to a access database (don't import them link them).  

Create a query in access using design view.

Bring all 26 linked tables into the query. Join all the subinventories to the master on the item number. Set all the joins so that all records from the master inventory are included.

Add all your field from the master inventory to the query. To do this drag the to the columns at the bottom of the design view screen.

Create one more column for the total "Total: table1!qty + table2!qty + table3!qty..........”

Is you do all that right the query should output something which looks very similar to your original master inventory sheet but with the totals you seek. You can the copy that back to excel if you really want to.

Why one would use spreadsheets for that much data is beyond me...that's why Access is around (Or...SQL Server...Oracle...etc..).



It can be done, but it'd be so much easier utilizing an actual database.



And those spreadsheets can be uploaded easily into Access.

I have never used access. I will try to get it all worked in.

I think sumif is the formula you seek. =sumif("column including item number"="item number","column including quantity "). Or something like that. I'm on an iPad so going off memory. Of course this would have to be done 25 times for the sub inventories but it's a start

I agree with the Access suggestions.
If you are stuck (i.e. my IT won't give us Access licenses any more ) try this:





 

Time to get to a real database for this kind of thing.

VLOOKUP and HLOOKUP, google it, I ain't got time to explain.

Sounds like a HLOOKUP or VLOOKUP will take care of that

Vlookup

ETA: Beat by mere seconds... listen to us. VLOOKUP is what you seek.

Ok, this project has been a side project and I have tried a numerous sumifs and vlookups based on the input here.

I am currently trying to build a database in access by importing the excel tables, but I am starting to feel lost in access.

Import the Excel source into Access.  What needs to be done beyond that?

I have importe all 25 sheets to access.

I made the primary key the Item stocking number.

Then I built the relationships each of the 24 sub sheets. Each sub sheet is related to the master sheet by the item stocking number, which is also the primary key.

What I still can't figure out how to do is to populate the Total quantity in each row of the master sheet (designated by the item stocking number) from all 24 subsheets.

Build the following expression in your query.

Total Quantity: Table1!qty+Table2!qty+Table3!qty+Table4!qty.........

Edit: there should be square brackets around each table and field name, but that breaks the board.

And that will build it vs the primary key?

Also, I am not seeing a place to type in logic functions like excel.

You need to include the primary key as a different another field in the query. but assuming that is done and your joins are correct( they should all look like arrows pointing from the master tables key  to each sub table's key) the yes it will work. You can also include any other fields you want to see on the report from the master table to give yourself a readable inventory report.

If for some reason that doesn't work post a screenshot of your query design.

Buy a good ERP.  No need to reinvent the wheel.

No Idea what an ERP is. What I have available is microsoft access 2013.

Scroll down to about the middle of this page and read the part about creating a calculated field in a query




If you still don't see it can I see a screen shot of what you've got so far. I have enough trouble teaching coworkers to do vlookups in excel. Giving access help via message board is bound to be harder.

ERP stands for enterprise resource planning. I'm not aware of ERP software package that could be used of the shelf and get this done without far more effort then access will require. They tend to be semi custom software packages implemented by a team of programmers and users and require months or years of work. A erp package would make this an easy thing to do once it was setup.