Small Inventory Spreadsheet?

[jjaggii]

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.

 

[jmacleary]

Hello there. I would really like to help you on this, but the req

 

[jjaggii]

Hello
Thanks for replying but the reply seems to be incomplete?

 

[jmacleary]

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]

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.

 

[jamiemarie]

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
	A	B	C	D	E
1	Driver 1	Bread	Sugar	Coffee	Tea
2	Delivery 1	2	3	1	2
3	Delivery 2	2	3	1	2
4
5
6	Driver 2	Bread	Sugar	Coffee	Tea
7	Delivery 1	2	3	1	2
8	Delivery 2	2	3	1	2
9
10	Total USED	8	12	4	8
Sheet1

PERSONAL.XLSB
	A	B	C
1	Before Input to Drivers Delivery Sheet	Inventory	Stock Level
2		Bread	400
3		Sugar	300
4		Coffee	200
5		Tea	100
6
7
8	After Input to Drivers Delivery Sheet	Inventory	Stock Level
9		Bread	392
10		Sugar	288
11		Coffee	196
12		Tea	92
Sheet4

 

[jjaggii]

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

 

[jmacleary]

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
	A	B	C
1	Initial Stock Levels	Inventory	Stock Level
2		Bread	400
3		Sugar	300
4		Coffee	200
5		Tea	100
6
7
8	After Input to Drivers Delivery Sheet	Inventory	Stock Level
9		Bread	392
10		Sugar	288
11		Coffee	196
12		Tea	92
Inventory

Cell Formulas
Range	Formula
C9:C12	C9	=C2-HLOOKUP(B9,Totals,2,FALSE)

Named Ranges
Name	Refers To	Cells
Totals	=Deliveries!$1:$3	C9:C12

Book1
	A	B	C	D	E	F
1	Totals	Bread	Sugar	Coffee	Tea
2		8	12	4	8
3
4	Driver 1	Bread	Sugar	Coffee	Tea
5	Delivery 1	2	3	1	2
6	Delivery 2	2	3	1	2
7
8
9	Driver 2	Bread	Sugar	Coffee	Tea
10	Delivery 1	2	3	1	2
11	Delivery 2	2	3	1	2
Deliveries

Cell Formulas
Range	Formula
B2:E2	B2	=SUM(B4:B999)

 

[jjaggii]

Many thanks for this, its exactly what I was hoping for. Very very much appreciated.

 

[jmacleary]

You're welcome and thanks for the feedback.