Formular to add up qtys from another sheet

WWARRIOR2018

Hi Guys

I have a main smartsheet with all my products and current stock levels on, and another smartsheet which has all the product movements and quantitys, this sheet will be the one users update

What I need is for the stock levels for each product on the main smartsheet to update based on the movements file. In both sheets we have the product name which can be used to look up, and in the movements file it has the value.

The item could be listed in the movements file a number of items so it needs to search and add up the quantitys if that makes sense.

movementd.png

main.png

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Assuming the other option is "Goods Out", you would use a SUMIFS to get all of the "In" and subtract from that a SUMIFS of the "Out".

=SUMIFS({Qty}, {Item}, @cell = Item@row, {In / Out}, @cell = "Goods In") - SUMIFS({Qty}, {Item}, @cell = Item@row, {In / Out}, @cell = "Goods Out")

Paul Newcome

Each {Cross Sheet Reference} should be created following the appropriate steps.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

All comments

Paul Newcome

Assuming the other option is "Goods Out", you would use a SUMIFS to get all of the "In" and subtract from that a SUMIFS of the "Out".

=SUMIFS({Qty}, {Item}, @cell = Item@row, {In / Out}, @cell = "Goods In") - SUMIFS({Qty}, {Item}, @cell = Item@row, {In / Out}, @cell = "Goods Out")

WWARRIOR2018

Thanks Paul for the quick response, when there has been a goods out, the quantity will be a minus so all the formula needs to do is add up and remove the minus's if that makes sense. I did try the above formula but to be honest got a bit lost when trying to apply it to my smartsheet

Paul Newcome

Right. That's what my formula is doing. Which part did you run into trouble with when implementing it?

WWARRIOR2018

Paul I think I am just having a mini breakdown and forgotten where to insert the sheet names into the formula

Paul Newcome

Each {Cross Sheet Reference} should be created following the appropriate steps.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

WWARRIOR2018

Paul thankyou for all your help, def was having a bad day but all good now thanks

Paul Newcome

Happy to help. 👍️

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.