Help creating formula to update inventory automatically

The following is our brochure requests sheet. Right now we manually update our inventory each time we receive a request.

Here is a snapshot of the new inventory sheet.

I'd like to create a formula that automatically subtracts 100 from Spanish Qty each time the Request Completed is checked, the Request Denied column is blank and Brochures Confirmation column = Spanish Brochures.

Any help would be greatly appreciated.

Find more posts tagged with

Forms

Sheets

Formulas

Accepted answers

Mike TV

@Christina S.

Switch your formula to:

=[Spanish Qty Baseline]# - (COUNTIFS({Brochure Requests Sheet Request Completed}, 1, {Brochure Requests Sheet Request Denied}, ISBLANK(@cell), {Brochure Requests Sheet Brochures Confirmation}, CONTAINS("Spanish Brochures", @cell), {Brochure Requests Sheet Submission Date}, >DATE(2023, 6, 8)) * 100)

All comments

Mike TV

@Christina S.

I'm sure there are multiple ways to do this. Here's my way.

On your Inventory Sheet, create a Sheet Summary entry called 'Spanish Qty Baseline'. Put the amount in there that you'd have in quantity if the Brochures Request Sheet were completely empty. I put in 2000 in mine.

Then in the Spanish Qty column put in this formula:

Everything works for me on my example sheets:

Christina S.

Thank you, @Mike TV! That seems to have worked, but now I've run into a small problem. Our Brochures Requests sheet has requests that date back to 2019. However, I only want to count new requests from today onward. Is that feasible?

Mike TV

@Christina S.

Switch your formula to:

Christina S.

Perfect! That worked! Thank you, @Mike TV

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.