In attached workbook, I added 2 worksheets - Commodities and DataEntry. Both worksheets contain a defined Table (Commodities was needed, so I could define a dynamic commodity list for data validation, and also for avoiding defining a dynamic range for whole Specs3 table for VLOOKUP's).
At top of DataEntry sheet I left some empty rows. You can put a table like in your original sheet there, or you can enter SUBTOTAL formulas there to SUM or COUNT column entries filtered using autofilter feature. NB! When you enter something at top of page, leave the row above Table empty - otherwise Excel can at some point decide, that header row is above current one and expand to top - making a mess of all formulas.
You can use table on sheet DataEntry as source for any reports you can think off.
To add new rows into defined Tables (adding all formulas, formats, data validations, etc. automatically):
a) select right lower cell of Table, and press TAB;
b) select any cell in Table, select Design from top Menu, click Resize Table button at left of menu ribbon, and change row number in table range;
c) simply add e.g. new EntryDate into next row.
You can change column names in Tables at will - the formulas adjust automatically. But when column names contain spaces, or special characters, the formulas are difficult to read, as mostly only a part of formula will be visible on formula field - you have to scroll through formula character-wise in edit mode to pass space/special character, to see next part of formula.
I used different format for dates/times, as yours were for me difficult to read. Format date/time columns as you prefer - it doesn't affect formulas.
|