Help creating formula to update inventory automatically

Christina S.

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.

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)

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:

=[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:

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:

=[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)

Christina S.

Perfect! That worked! Thank you, @Mike TV