Warning

 

Close

Confirm Action

Are you sure you wish to do this?

Confirm Cancel
PSA
Member Login

Site Notices
Posted: 8/12/2015 1:34:58 AM EDT
[Last Edit: 9/4/2015 3:06:55 PM EDT by Gmezz4]
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.
Link Posted: 8/12/2015 2:09:16 AM EDT
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.
Link Posted: 8/12/2015 2:15:36 AM EDT
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

Link Posted: 8/12/2015 2:22:21 AM EDT
[Last Edit: 8/12/2015 2:22:46 AM EDT by replicators]
Sounds like a job for sumif() or countif()
Link Posted: 8/12/2015 2:23:08 AM EDT
[Last Edit: 8/12/2015 2:24:21 AM EDT by sonaliel]
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.
Link Posted: 8/12/2015 2:29:10 AM EDT
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.

Link Posted: 8/12/2015 7:41:20 AM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By sonaliel:
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.
View Quote


QFT
Link Posted: 8/12/2015 10:56:00 AM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By sonaliel:
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.

View Quote

This will definitely be a living document.
Link Posted: 8/12/2015 10:57:09 AM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By sonaliel:
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.
View Quote

it is the last situation, where all the items are on the master and only some items are in the sub sheets.
Link Posted: 8/12/2015 10:58:48 AM EDT
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.
Link Posted: 8/12/2015 11:21:20 AM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:

it is the last situation, where all the items are on the master and only some items are in the sub sheets.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:
Originally Posted By sonaliel:

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


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.


Link Posted: 8/12/2015 11:26:07 AM EDT
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.
Link Posted: 8/12/2015 10:54:30 PM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By robplumm:
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.
View Quote

I have never used access. I will try to get it all worked in.
Link Posted: 8/12/2015 11:13:15 PM EDT
[Last Edit: 8/12/2015 11:23:17 PM EDT by TXHORNEDFROG]
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
Link Posted: 8/12/2015 11:18:57 PM EDT
[Last Edit: 8/12/2015 11:23:25 PM EDT by Tholo]
I agree with the Access suggestions.
If you are stuck (i.e. my IT won't give us Access licenses any more ) try this:





holy crap, that didn't work

MasterPNAuthorized QtyTotal Qty                Sheet2 On Hand                                  Sheet3 On Hand
A                       = sum of cells to right= SUMIF( Sheet2!$A:$A, Sheet1!$A2, Sheet2!$B:$B )= SUMIF( Sheet2!$A:$A, Sheet2!$A2, Sheet2!$B:$B )







 
Link Posted: 8/12/2015 11:24:26 PM EDT
Time to get to a real database for this kind of thing.
Link Posted: 8/12/2015 11:29:19 PM EDT
VLOOKUP and HLOOKUP, google it, I ain't got time to explain.
Link Posted: 8/12/2015 11:29:33 PM EDT
Sounds like a HLOOKUP or VLOOKUP will take care of that
Link Posted: 8/12/2015 11:29:33 PM EDT
[Last Edit: 8/12/2015 11:30:23 PM EDT by 32DOHC]
Vlookup

ETA: Beat by mere seconds... listen to us. VLOOKUP is what you seek.
Link Posted: 9/4/2015 1:23:14 PM EDT
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.
Link Posted: 9/4/2015 1:49:17 PM EDT
Import the Excel source into Access.  What needs to be done beyond that?
Link Posted: 9/4/2015 2:57:08 PM EDT
[Last Edit: 9/4/2015 2:58:57 PM EDT by Gmezz4]
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By burnprocess:
Import the Excel source into Access.  What needs to be done beyond that?
View Quote

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.
Link Posted: 9/4/2015 4:42:02 PM EDT
[Last Edit: 9/4/2015 4:46:23 PM EDT by sonaliel]
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:

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.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:
Originally Posted By burnprocess:
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.
Link Posted: 9/4/2015 4:53:50 PM EDT
[Last Edit: 9/4/2015 5:03:19 PM EDT by Gmezz4]
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By sonaliel:


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.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By sonaliel:
Originally Posted By Gmezz4:
Originally Posted By burnprocess:
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.
Link Posted: 9/4/2015 5:03:32 PM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:


And that will build it vs the primary key?
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:
Originally Posted By sonaliel:
Originally Posted By Gmezz4:
Originally Posted By burnprocess:
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?


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.

Link Posted: 9/4/2015 5:34:28 PM EDT
Buy a good ERP.  No need to reinvent the wheel.
Link Posted: 9/4/2015 5:45:21 PM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By hardcase:
Buy a good ERP.  No need to reinvent the wheel.
View Quote

No Idea what an ERP is. What I have available is microsoft access 2013.
Link Posted: 9/4/2015 10:27:57 PM EDT
Discussion ForumsJump to Quoted PostQuote History
Originally Posted By Gmezz4:

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

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.
Top Top