I had a look at your attached table. Some remarks.
Keep all summaries at top of page - then you don't have to bother about rows you may add at bottom of table;
Don't use full-column references when you can avoid this;
Don't use fixed Named Ranges whenever the range dimensions may change - use dynamic Named Ranges;
When you aren't planning to use the workbook as shared, use defined Tables instead Named Ranges. Defined Tables have specific syntax to refer to its various elements (header, datarange, datarange column, cell in same row, etc.), adjust formulas automatically whenever you change Table headers, used formulas usually aren't sensitive to order of columns in table, adding some data into any cell immediately below last row of Table expands the Table automatically, and unless you have several different settings in same column, all formulas, formats, and validations are automatically expanded whenever new rows are added.
As example, in attached file I designed a sheet Estimate similar to your one.
Both Tables on sheets Items and Estimate have a couple of helper columns added (colored differently, they are meant to be hidden). And the Table on sheet Items has also column SelectedQty, where you can enter the quantities for items, you want to include into selection.
On sheet Estimate, the Table is automatically filled with selected items, their quantities, and monetary amounts.
For sheet Estimate, the Print Area is automatically adjusting to number of rows in Estimate Table.
The Total on sheet Estimate shows total of filtered amounts, i.e. when you set some autofilter to Estimate Table, only visible Amounts are totallied.
I didn't use it, but as example I defined a dynamic Named Range nItemTbl. It is possible to redesign all formulas to refer to this range instead of table (but you lose some functionality of-course). I myself prefer Tables. There are cases where I have to use Names too (validation lists and conditional formats don't recognize Tables), but then I mostly define a Table, and then a Named range as some part of this Table.
|