Selected Answer
Nitil
This replaces my earlier answer (now deleted) and refers to your Revision 2 workbook.
One (fairly) easy solution is to use a Pivot Table- I've added one in cells F4:H7, under a heading "Today's Dashboard". I've given the steps to do that (further down this answer).
My solution also uses a Table for your time/ quantity data so adding extra rows (with the formatting) is simply a matter of typing in the row immediately below the table. You do this by:
- Highlighting the table then use "Insert table" to convert to a table .I also gave the table the Name "Today" under Name Manager and..
- used Create from Selection on header row to name the columns)
- Under the Table Design tab, unclick Filter Button (unless you need it).
The second advantage (especially of step 2) above is that the formula for percentage can also be automatically filled down when a new row is added. Tha's done by adding a "Calculated Column" where your cell-related formula is converted to one with structured references. Instead of your formula for D5:
=C5/(24*(B5-$B$1)/6.15*VLOOKUP($A5,$J$5:$M$7,2,FALSE))
I deleted your cells and added a column where D5 (and the whole column) column gets the formula:
=[@[Current day Stock until time]]/(24*([@[Stock production time]]-$B$1)/VLOOKUP([@Name],$J$5:$M$7,3,FALSE)*VLOOKUP([@Name],$J$5:$M$7,2,FALSE))
This works the same but when you add a new row (by typing just below the table, as I said), that formula is copied down (along with conditional formatting).
A slight problem with this is that the column format in a new row defaults to time (I believe this is bacause Microsoft say "When you manipulate dates, the return type of the calculated column must be set to Date and Time." so I fix that in the WorksheetChange event macro that's neeeded to overcome the downside of using a PivotTable (that normally has to be refreshed manually). I've got around that by using a WorksheetChange event macro which corrects things when a value is changed in the column of the table headed "Current day Stock until_time":
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Current_day_Stock_until_time")) Is Nothing Then Exit Sub
' force Calculated Column format to percentage
Range("Current_c.f._historic_average_daily_stock_produced_to_time").NumberFormat = "0%"
' refresh Pivot Table
PivotTables("PivotTable2").RefreshTable
End Sub
Hope this works for you (or Variatus or others propose something better).
.
Creating the Pivot Table:
- In cell F4, insert Pivot Table with:
- Table/Range = Today (or select table range)
- Location = Existing Worksheet
- In PivotTable Fields, drag Name to Rows
- Drag "Current c.f Historic..." to Values (it will default to "Sum of..."- ignore for now)- do that a second time
- Pick upper Sum of and click Down arrow/Value Field Settings then
- Change Summarize by from Sum to "Min"
- Click Number Format button then to change Number to Percentage and set Decimal Places to 0
- Repeat for lower Value but Change Summarize by to "Min"
- Edit column headings (using "Name " with added trailing space since a label should not match a Pivot Table field name) and adjust column widths, formatting etc.
