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?

Find more posts tagged with

Forms

Sheets

Formulas

Accepted answers

All comments

Larry

@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.

bsaucedo

@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.

Larry

@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?

bsaucedo

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?

bsaucedo

@Larry

Michelle Choate 2

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)).

bsaucedo

@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.

Michelle Choate 2

And since these might be different items, you might also want to add Name:Name, Name@row into the COLLECT formula.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.