An alternative without coding - you can do all this with formulas only.



I left all your sheets as they were and added new ones.

You can keep several inventories. I added QtyPurchased field so user can follow, were ordered parts received or not.

The print area definition for PartOrder sheet is based on Dynamic Name, so you can have any number of report rows prepared, but only rows not empty are printed.

In case you aren't familiar with defined Tables, to add a new row into table:
1. Select right bottom cell of Table, and press Tab;
2. Select any cell in Table, activate Design menu, and at left of header click Resize Table - set the number of rows you want in Table;
3. Enter something into next row at bottom of table (avoid columns with formulas, and columns with Data Validation for better too).

When you add a new row(s) into Table, Data Validations, Conditional Formatting, and formulas (latter in case the whole column has same formula) are copied into new row(s) automatically.

Some columns are hidden, as they are there to keep data needed for calculations and user don't need to see them. You can unhide them to to understand, how all works, of-course.

Attached Files

Inventory.xlsx (22.1 KB, 16 views)