Tough Inventory Question

jtchan

New Member
Joined
Aug 27, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!!!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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,
 
Upvote 0
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
AB
1NameDate Time
2Charles01-Sep-20 12:12
3Charles01-Sep-20 14:00
4Vanessa02-Sep-20 16:24
5Xavier03-Sep-20 18:48
6Felicity04-Sep-20 21:12
7Xavier05-Sep-20 23:36
8Felicity07-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
ABCDEFGHIJKLM
1Use per SessionTotalsStart/Current Inventory
2NameCans of BallsStringsWaterCans of BallsStringsWaterCans of BallsStringsWater
3Charles422844Inventory Start100100100
4Sarah225000Inventory Current738780
5Vanessa314314
6Xavier5141028
7Felicity332664
8000
Pros
Cell Formulas
RangeFormula
F3:H8F3=COUNTIF(Bookings!$A:$A,Pros!$A3)*Pros!B3
K4:M4K4=K3-SUM(Pros!F$3:F$99)
 
Upvote 0
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!!!!
 
Upvote 0
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
AB
1NameDate Time
2Charles01-Sep-20 12:12
3Charles01-Sep-20 14:00
4Vanessa02-Sep-20 16:24
5Xavier03-Sep-20 18:48
6Felicity04-Sep-20 21:12
7Xavier05-Sep-20 23:36
8Felicity07-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
ABCDEFGHIJKLM
1Use per SessionTotalsStart/Current Inventory
2NameCans of BallsStringsWaterCans of BallsStringsWaterCans of BallsStringsWater
3Charles422844Inventory Start100100100
4Sarah225000Inventory Current738780
5Vanessa314314
6Xavier5141028
7Felicity332664
8000
Pros
Cell Formulas
RangeFormula
F3:H8F3=COUNTIF(Bookings!$A:$A,Pros!$A3)*Pros!B3
K4:M4K4=K3-SUM(Pros!F$3:F$99)
Thank you so much! Working perfectly!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top