Inventory help

  • Thread starter Thread starter cypher67
  • Start date Start date
C

cypher67

I need help with sorting items that are entered , batching simular item
by descriptions from multiple cells , and then totalling th
quantities. example
Cell a would have colors of cars, cell b would have the number o
doors, cell c would have the number of cylinders the motor has,cell
would be for 4 wheel or 2 wheel drive ,cell e would contain a quantit
of how many of this kind I have.
I need to batch all the cars that match all 4 cells,(a,b,c,d) and
need the sum of cell e totaled together as new items are entered o
taken away.
This is what would seem to be a simple inventory worksheet but I jus
cant figure out a way to batch and total them.
If anyone has any links , ideas or help on a input box , to help kee
users from inputing items in the cells I would be greatful for you
help.
Thanks in advance
 
Cypher,

Use a pivot table. There are many good examples on the web at
Microsoft.com, or jsut read help.

HTH,
Bernie
MS Excel MVP
 
cypher67,
You could use the SUMPRODUCT function to get the totals you need.

Assume your inventory table is on sheet1 and your summary table is on sheet2. The folwoing formula will count the matches in A2, B2, C2, and D2 within the corresponding columns on sheet1.

=SUMPRODUCT((Sheet1!$A$2:$A$10000=A2)*(Sheet1!$B$2:$B$10000=B2)*(Sheet1!$C$2:$C$10000=C2)*(Sheet1!$D$2:$D$10000=D2))


I'm not sure how you want to use an input box, but you might want to look at Data>Validation. You can set up the cell to only allow a user to select from a specific list.


Good Luck,
Mark Graesser
(e-mail address removed)


----- cypher67 > wrote: -----

I need help with sorting items that are entered , batching simular items
by descriptions from multiple cells , and then totalling the
quantities. example
Cell a would have colors of cars, cell b would have the number of
doors, cell c would have the number of cylinders the motor has,cell d
would be for 4 wheel or 2 wheel drive ,cell e would contain a quantity
of how many of this kind I have.
I need to batch all the cars that match all 4 cells,(a,b,c,d) and I
need the sum of cell e totaled together as new items are entered or
taken away.
This is what would seem to be a simple inventory worksheet but I just
cant figure out a way to batch and total them.
If anyone has any links , ideas or help on a input box , to help keep
users from inputing items in the cells I would be greatful for your
help.
Thanks in advance,
 
Back
Top