Small Inventory Spreadsheet?

jjaggii

New Member
Joined
Apr 11, 2017
Messages
11
Hi

I would appreciate any help for something that I cant figure out.

My scenario is this

1) I have a inventory worksheet with various items in column 1 and stock levels of those items in column 2.

2) I have a delivery sheet for each of 12 drivers

3) The driver’s sheet has a delivery number in the first column. Column B to G (for example) will have item name as column headers. Each delivery will have the number of each item which I will insert.

4) What I am looking to do is when I insert the number of each item into the driver’s delivery sheet, I want the item in the Inventory worksheet to decrease.

For example – The inventory has 300 of Item A and 400 of Item B.

On the driver’s delivery sheet I insert 2 of item A, and 3 on item B.

I would like to see the inventory worksheet to decrease to 298 of Item A and 397 of Item B.

This is for a charity who deliver food parcels to shielded people during the lockdown, and they are struggling with stock control. Any help appreciated.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hello there. I would really like to help you on this, but the req
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Oops. Something went totally wrong with my reply, and now I can't read what you've written properly either. Anyway, my original reply should have been: can you post a small sample of the inventory sheet, and one of the drivers sheets? I am thinking it may be tricky to do, but I will have a look if you can post the samples.
 

jjaggii

New Member
Joined
Apr 11, 2017
Messages
11

ADVERTISEMENT

Thanks for coming back to me. I cannot see an option to upload a spreadsheet so I have done this by images.
The image Inventory shows the before and after totals after I have input into the driver delivery sheet. The driver deliver sheet image is called Driver. This shows the total of each product being delivered. Thanks again for your help.
 

Attachments

  • Driver.png
    Driver.png
    7.8 KB · Views: 8
  • Inventory.png
    Inventory.png
    7.9 KB · Views: 8

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
You can use the Mr. Excel XL2BB tool to add data that is able to be copied. If you look in the comment box, you will see the button for the tool. I'm bored at work and your sample was small, so I took the liberty of taking your images, typing the data in excel and below is the XL2BB copy.

PERSONAL.XLSB
ABCDE
1Driver 1BreadSugarCoffeeTea
2Delivery 12312
3Delivery 22312
4
5
6Driver 2BreadSugarCoffeeTea
7Delivery 12312
8Delivery 22312
9
10Total USED81248
Sheet1


PERSONAL.XLSB
ABC
1Before Input to Drivers Delivery SheetInventoryStock Level
2Bread400
3Sugar 300
4Coffee200
5Tea100
6
7
8After Input to Drivers Delivery SheetInventoryStock Level
9Bread392
10Sugar 288
11Coffee196
12Tea92
Sheet4
 

jjaggii

New Member
Joined
Apr 11, 2017
Messages
11

ADVERTISEMENT

JamieMarie, thanks for educating me, andI'm happy I alleviated your boredom for a short while.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hello there. Thanks to JamieMarie for pointing you to xl2bb. I should have done so myself. Anyway, I have done a bit of restructuring on your 2 sheets and have come up with what should be workable, understandable, and expandable. Basically, I have moved the totals on the drivers sheet to the top, and made them sum a long range below. Extra commodities can be added, just make sure the names are identical for the lookup to work. In order to 'refresh' your sheet ready for the next days deliveries, just copy the after values to the before values (use paste special... values).

Book1
ABC
1Initial Stock LevelsInventoryStock Level
2Bread400
3Sugar 300
4Coffee200
5Tea100
6
7
8After Input to Drivers Delivery SheetInventoryStock Level
9Bread392
10Sugar288
11Coffee196
12Tea92
Inventory
Cell Formulas
RangeFormula
C9:C12C9=C2-HLOOKUP(B9,Totals,2,FALSE)
Named Ranges
NameRefers ToCells
Totals=Deliveries!$1:$3C9:C12

Book1
ABCDEF
1TotalsBreadSugarCoffeeTea
281248
3
4Driver 1BreadSugarCoffeeTea
5Delivery 12312
6Delivery 22312
7
8
9Driver 2BreadSugarCoffeeTea
10Delivery 12312
11Delivery 22312
Deliveries
Cell Formulas
RangeFormula
B2:E2B2=SUM(B4:B999)
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,112
Messages
5,546,009
Members
410,720
Latest member
SSL
Top