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

Lemoni

Hi all,
I’m looking for advice on the best workflow to manage inventory deductions.

I currently have two sheets:
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? My worry is if I archive the weekly sheet, we would have 52 archived sheets per year and it would be very difficult to manage.

Any help, idea would be very welcomed. Thanks a lot!

Naeem Ejaz

• Instead of deleting rows:
  • Mark rows as “Processed = Yes”

• Formula only subtracts rows where Processed = Yes

• OR

• One permanent “Usage History” sheet
  Automation: “When row added → copy row”
  Weekly scanner sheet can be safely cleared
  Master Stock uses formulas against history