How to maintain permanent inventory levels when source data is deleted weekly

Berbay

Hi all, I am looking for advice on the best workflow to manage inventory deductions. I currently have two sheets where

• Sheet 1 (Master Stock List): Contains hundreds of items, locations, and catalog numbers.
• Sheet 2 (Weekly Usage): Operators log items used during the week by scanning QR codes.

Currently, Sheet 1 uses a SUMIF cross-sheet formula to subtract the quantities found in Sheet 2. The problem is that Sheet 2 is cleared every week to make room for new entries. When those rows are deleted, my stock levels in Sheet 1 "reset" because the formula no longer sees the data.

How can I keep Sheet 1 updated permanently without the numbers breaking when the weekly scans are cleared? Is there an automation or archiving workflow you would recommend?

Kerry St. Thomas

My first thought: don't clear Sheet 2. Instead, include a "Created Date" auto column, that you can use to derive a "Week Of" as a column formula (always return Monday of the week in which an entry is).

Alternately, if you MUST clear Sheet 2, change the structure a little bit. Add a check box column "Archived." Add a Copy Row automation - Trigger is On a Date (set it up to run every day at the middle of the night), Condition is "Archived = Not Checked", Action is "Copy Row" to Sheet 3 (that doesn't currently exist), and second Action is Update a Value to check the "Archived" box.

ESSENTIALLY, what you'll do is create a cumulative sheet - Sheet 3. Then you'll basically just need to rebuild your formulas on Sheet 1 to point to Sheet 3 instead of Sheet 2.

KEEP IN MIND: You'll want to think about the whole process - how many rows will be generated, and over what period of time? There will likely come a point in time when you'll need to reckon with sheet capacity.

This is just one of probably multiple options - just the one that came to the top of mind for me, and without requiring much of a lift to change things around for you. Whether you go this route or do something different, good luck and happy Smartsheeting!