Adding row values based on date

Hello Smartsheet Community,

I am trying to create a reconciliation system in Smartsheet for the on-hand count vs the dispensed count of an item. The idea is to have the end user submit a morning count of 3 different products every morning. Through another form, submit a dispensed amount for the products throughout the day (will be multiple form submissions per day). A 3rd form will be filled out by the end user for restock counts.

In the Smartsheet, I am trying to take the morning count minus the dispensed amounts through the day and reconcile it to show a match or discrepancy against the next morning's count,

Any help on this would be greatly appreciated

Thank you!

Find more posts tagged with

Accepted answers

All comments

SmartLew

Hi,

Where are you trying to show the final figure? In a separate sheet? Will you have a static list of items to count against?

Is this along the right lines?

Form intake:

Three different forms capturing the date , item, type of submission and count

Summary Sheet:

Showing by item:

The sum of morning count for todays date

=SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Morning Count", {intake sheet date submitted}, TODAY())

sum of morning count minus dispensed count for yesterdays date, (not sure if this should also add the count of restock? I haven't included that in the formula so let me know!)

=SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Morning Count", {intake sheet date submitted}, TODAY(-1)) - SUMIFS({intake sheet count}, {intake sheet item}, ITEM@row, {intake sheet type}, "Dispensed", {intake sheet date submitted}, TODAY(-1))

and then the difference between those two columns.

=[This Morning Count]@row - [Yday Morning-Dispensed]@row

Is this what you are after?

SmartLew

https://community.smartsheet.com/discussion/comment/344305#Comment_344305

Just realised that in fact you most likely want the reconciliation in the next mornings row in the intake sheet, doing away with the summary sheet:

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.