Help setting up a daily tracker
Hello!
My team is attempting to create a sheet and form that counts the amount of inventory of a product we are overselling per day. We essentially would have the inventory start with 15 units per day, we are going to request the number they sold for each entry. The goal is to have the number they enter in the form will be subtracted from the available amount of inventory left for the day (starting number is 15).
The available inventory (15 per day) is on the form and is hidden but trying to figure out how to make the remaining inventory formula stick, it won't allow me to make it a column formula.
Currently this is the formula I am using on Remining Inventory : =[Available Inventory]@row  SUM([Units Sold]1:[Units Sold]@row).
I need to count the units sold per day this will be fed to a report that end users can see if inventory remains and if so how much before they make the decision to oversell. Any Idea how I can do this?
Answers

@bsaucedo Do you want the Remaining Inventory Value to be the difference of Available Inventory@row & ALL Units Sold?
=[Available Inventory]@row  SUM([Units Sold]:[Units Sold])
That may be oversimplifying but I am unclear on when the frequency of form entries here.
Larry Cummings
https://primeconsulting.com/
Principal Consultant  Prime Consulting Group 
@Larry Yes the remaining inventory would be the difference between the available and hopefully all units sold for that day. I have a created date column is there a way to have it find the difference if it is rows of the same date?
The goal is to have this be a quick reference for people to see how much inventory remain in real time. Entries will only be when a sale occurs for tracking purposes once the product is in the additional overflow inventory.

@bsaucedo So, in my formula the Remaining Inventory would show 12 for both rows in your screenshot but that is NOT what you want to see?
Larry Cummings
https://primeconsulting.com/
Principal Consultant  Prime Consulting Group 
I wouldn't mind if it counted down per row (if possible). The formula you given will that count each day's total sales separately?

I believe for this to happen you would need a 'Row #' column to help with this.
You will need to add a date column, 'Created', with the formula =DATEONLY([Created Date]@row). Those are the very first steps to get to your formula.
So we are one step closer with =[Available Inventory]@row  SUM(COLLECT([Units Sold]:[Units Sold], Created:Created, Created@row)).
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!

@Michelle Choate 2 Hello! This got me closer! The only thing is we would like it to follow each new entry automatically (see the progress / count down of available inventory) after each new entry. Ideally, we would like it to not change the previous lines remaining inventory for the specific date.

And since these might be different items, you might also want to add Name:Name, Name@row into the COLLECT formula.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!