Design a WPS Spreadsheet Inventory Sheet Without Overwriting Formulas
Question details
Create an inventory worksheet that allows stock movements to be entered without replacing the formulas used to calculate current quantities.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user needs to track opening inventory, receipts, transfers, and adjustments across locations while retaining a movement history.
- Observed behavior
- Using one cell for both manual stock values and inventory formulas causes formulas to be overwritten and removes the transaction record.
Decide on consistent names for every stock location, such as Warehouse A and Store 1, before creating formulas. Use one unique SKU or item code for each inventory item so transaction totals can be matched accurately.
Separate inventory transactions from calculated stock balances
Use this approach to preserve every stock movement and calculate each location's balance without typing over formula cells.
Create an Inventory table for opening quantities and a separate Transactions table for receipts, transfers, and adjustments. Enter movements only in the Transactions table; keep the Current Stock column formula-driven.
For transfers, enter the source location in the From Location column and the receiving location in the To Location column. For receipts, use a source such as Supplier; for stock leaving inventory, use a destination such as Customer.
In WPS Spreadsheet, add a sheet named Inventory and create columns for SKU, Item Name, Location, Opening Quantity, and Current Stock. Select the range and choose Insert > Table to make the inventory list easier to manage.
Add a sheet named Transactions and create columns for Date, SKU, Movement Type, From Location, To Location, and Quantity. Record each receipt, transfer, issue, or adjustment as a new row instead of changing an inventory balance manually.
In the Inventory table's Current Stock column, enter a formula based on the SKU and location, such as =D2+SUMIFS(Transactions!$F:$F,Transactions!$B:$B,$A2,Transactions!$E:$E,$C2)-SUMIFS(Transactions!$F:$F,Transactions!$B:$B,$A2,Transactions!$D:$D,$C2). This adds incoming quantities and subtracts outgoing quantities for that item and location.
Select the formula cell in Current Stock and drag the fill handle down, or use Home > Fill > Down, so every item-location row calculates its own balance.
Protect calculated cells from manual edits
Use sheet protection after the inventory layout is complete to prevent users from accidentally replacing stock formulas.
Build a protected, formula-based inventory tracker in WPS Spreadsheet
WPS Spreadsheet lets you keep inventory movement entries separate from calculated balances. Use tables and SUMIFS formulas to calculate stock by SKU and location, then protect formula cells while leaving transaction-entry cells available to users.
- 1. Start an inventory workbook: Open WPS Spreadsheet and create separate worksheets named Inventory and Transactions.
- 2. Add inventory and movement columns: Enter SKU, location, and opening quantity on Inventory, then add date, SKU, source, destination, and quantity columns on Transactions.
- 3. Use formulas for stock balances: Add a SUMIFS-based formula in the Current Stock column to add inbound movements and subtract outbound movements.
- 4. Convert lists to tables: Select each data range and choose Insert > Table so new rows can be managed consistently.
- 5. Protect the calculation sheet: Use Review > Protect Sheet after unlocking only the cells intended for transaction entry.
Frequently Asked Questions
Why should I not type a new stock quantity into a formula cell?
Typing into a formula cell replaces the formula with a fixed value. Enter the stock movement as a new row in the Transactions sheet instead, so the Current Stock formula can recalculate and the movement remains in the history.
How do I record a transfer between two inventory locations?
Create one transaction row with the item SKU, quantity, source location in From Location, and receiving location in To Location. The balance formula subtracts the quantity from the source and adds it to the destination.
Can I use SUMIFS to calculate stock for each SKU and location?
Yes. Use SUMIFS to total quantities where the SKU and To Location match the inventory row, then subtract a second SUMIFS total where the SKU and From Location match that row's location.
How can users enter inventory transactions without changing formulas in WPS Spreadsheet?
Unlock only the transaction-entry cells through Format Cells > Protection, keep formula cells locked, and then select Review > Protect Sheet. Users can enter movements in unlocked cells while locked formulas cannot be edited.
What should I use for receipts and stock leaving the business?
For a receipt, set From Location to a value such as Supplier and set To Location to the receiving warehouse. For stock leaving inventory, set From Location to the warehouse and To Location to a value such as Customer or Disposal.
