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.
Best Answer
-
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)
Answers
-
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:
=[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)) * 100)
Everything works for me on my example sheets:
-
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?
-
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)
-
Perfect! That worked! Thank you, @Mike TV
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …