Tough Inventory Question

[jtchan]

Hi! I run a tennis club and I was hoping to use excel to track some of my inventory. Basically, I have 10 pros. They each use a set number of items. For example, I use 2 cans of balls, 1 set of strings, and 3 bottles of water per lesson. Each coach has a different number, but I was wondering if there was a way for me to connect each amount to a Coach and when I type the Coach's name into a field, it would automatically subtract the variables from my overall inventory.

Sorry if that doesn't make a whole lot of sense. But here's an example:

Pro #1:
2 Cans of Balls, 1 Set of Strings, 3 bottles of Water

Lesson:
Yes

Pro #2
3 Cans of Balls, 2 Set of Strings, 2 Bottles of Water


Initial Total Inventory
100 Cans of Balls, 100 set of strings, 100 bottles of water remaining.

New Total Inventory
95 Cans of Balls, 97 Set of Strings, 95 Bottles of water remaining.


Thank you so much to whomever can help me!!!

 

[HaydenJalili]

Hi, Yes it is very easy, you just need to have a data sheet to identify foe each coach the quantities of each item that uses in each class, in the next sheet you just need to have the list of classes including the name of the coach, if you use Countif() formula in the coaches datasheet to find the number of class for each coach then you can easily multiply the number of classes for each coach to the item number for the same coach and you will have a total consumed number of item,

 

[Toadstool]

Hi JTChan,

I'm not exactly sure how you envision it working (it may need to use VBA for some scenarios) but let me describe one solution using Excel functions.

I'm going to assume that your have some kind of list of bookings from the Pros which you add to on a daily basis. I'll call this tab "Bookings".

JTChan.xlsx
	A	B
1	Name	Date Time
2	Charles	01-Sep-20 12:12
3	Charles	01-Sep-20 14:00
4	Vanessa	02-Sep-20 16:24
5	Xavier	03-Sep-20 18:48
6	Felicity	04-Sep-20 21:12
7	Xavier	05-Sep-20 23:36
8	Felicity	07-Sep-20 02:00
9
Bookings

On another tab you've got:

1. A list of the Pros and their consumable rate per session.
2. You can then count the Bookings for each name and calculate totals of each consumable type for all listed bookings.
3. You can enter your starting inventory and the current becomes that minus totals used so far.

JTChan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Use per Session				Totals					Start/Current Inventory
2	Name	Cans of Balls	Strings	Water		Cans of Balls	Strings	Water			Cans of Balls	Strings	Water
3	Charles	4	2	2		8	4	4		Inventory Start	100	100	100
4	Sarah	2	2	5		0	0	0		Inventory Current	73	87	80
5	Vanessa	3	1	4		3	1	4
6	Xavier	5	1	4		10	2	8
7	Felicity	3	3	2		6	6	4
8						0	0	0
Pros

Cell Formulas
Range	Formula
F3:H8	F3	=COUNTIF(Bookings!$A:$A,Pros!$A3)*Pros!B3
K4:M4	K4	=K3-SUM(Pros!F$3:F$99)

 

[jtchan]

Hi, Yes it is very easy, you just need to have a data sheet to identify foe each coach the quantities of each item that uses in each class, in the next sheet you just need to have the list of classes including the name of the coach, if you use Countif() formula in the coaches datasheet to find the number of class for each coach then you can easily multiply the number of classes for each coach to the item number for the same coach and you will have a total consumed number of item,

Hi, Yes it is very easy, you just need to have a data sheet to identify foe each coach the quantities of each item that uses in each class, in the next sheet you just need to have the list of classes including the name of the coach, if you use Countif() formula in the coaches datasheet to find the number of class for each coach then you can easily multiply the number of classes for each coach to the item number for the same coach and you will have a total consumed number of item,

Thank you!!!!

 

[jtchan]

Hi JTChan,

I'm not exactly sure how you envision it working (it may need to use VBA for some scenarios) but let me describe one solution using Excel functions.

I'm going to assume that your have some kind of list of bookings from the Pros which you add to on a daily basis. I'll call this tab "Bookings".

JTChan.xlsx
	A	B
1	Name	Date Time
2	Charles	01-Sep-20 12:12
3	Charles	01-Sep-20 14:00
4	Vanessa	02-Sep-20 16:24
5	Xavier	03-Sep-20 18:48
6	Felicity	04-Sep-20 21:12
7	Xavier	05-Sep-20 23:36
8	Felicity	07-Sep-20 02:00
9
Bookings

On another tab you've got:

1. A list of the Pros and their consumable rate per session.
2. You can then count the Bookings for each name and calculate totals of each consumable type for all listed bookings.
3. You can enter your starting inventory and the current becomes that minus totals used so far.

JTChan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Use per Session				Totals					Start/Current Inventory
2	Name	Cans of Balls	Strings	Water		Cans of Balls	Strings	Water			Cans of Balls	Strings	Water
3	Charles	4	2	2		8	4	4		Inventory Start	100	100	100
4	Sarah	2	2	5		0	0	0		Inventory Current	73	87	80
5	Vanessa	3	1	4		3	1	4
6	Xavier	5	1	4		10	2	8
7	Felicity	3	3	2		6	6	4
8						0	0	0
Pros

Cell Formulas
Range	Formula
F3:H8	F3	=COUNTIF(Bookings!$A:$A,Pros!$A3)*Pros!B3
K4:M4	K4	=K3-SUM(Pros!F$3:F$99)

Thank you so much! Working perfectly!