Attached is a version how I would design it (on added 3 sheets).
Orders are entered into single sheet (various months, and various years). As result, you design the workbook once, and the use it for as many months or years as you need it;
Tables on sheets are designed as Defined Tables. This allows user(s) easily filter tables (e.g. to enter returns for certain orders), and whenever a new entry is entered into table, the formats and formulas are expanded to new entry so long the column contains a single format or formula, and there are no gaps left into table when the record is added;
In current example, I used dates in OrderDate column, and calculated values for month number in format yyyymm in columns OrderPeriod and ReturnPeriod. In case the exact dates don't have any value for you, you can use month numbers instead;
On Report sheet, user can select order month from data validation list, and report for all SKU's in orders from this month is displayed. I designed a single report, but you can have any number of different reports designed;
Currently the returning of specific order can be done once and in full ordered quantity (based on your data). In case return can be partial (but still one-time event), you need to add into Orders table the column for return quantity, and edit the formula in ReturnQty column on Report sheet accordingly;
When for same order there can be several returns, especially in different periods, then you need a separate sheet with separate returns table there;
Currently I designed the formula for ReturnQty on Report sheet so, that only entries with same period as the report period are accounted. You can add to report columns, where returned quantities with OrderPeriod=report period are returned month later, 2 months later, etc.
|