Hi
Some on-fly example here. It's designed to use for a year. In next year you
save past years workbook with new name, clear all production entries, enter
new year number into Dates sheet, and can start with new years data.
Create a sheet Workers
Into cell A3 enter 'Workers'
A4 and down enter the list of workers (you can have additional data for
every worker in adjacent columns, but I'll ignnore it). (I started the
workers list from A4, so it'll be easier to design report sheets later.)
Define a dynamic named range Workers
=OFFSET(Workers!$A$4,,,COUNTIF(Workers!$A:$A,"<>")-1,1)
Create a sheet Models
A1='Models'
Into cell A2 and down enter the list of models you produce.
Define a dynamic named range Models
=OFFSET(Models!$A$2,,,COUNTIF(Models!$A:$A,"<>")-1,1)
Create a sheet Dates
A1='Year'
A2- enter the year the workbook is meant for.
C1='Dates'
C2=IF(YEAR(TODAY()-15)<>$A$2,"",DATE(YEAR(TODAY()-15),MONTH(TODAY()-15),1))
C3=IF(OR(YEAR(IF(C3="",TODAY()+1,C3)+1)<>$A$2,IF(C3="",TODAY()+1,C3)+1>TODAY
()),"",C3+1)
Copy C3 down (At least to cell C46, I think. You must get a list of dates
starting with 1st of current or previous month, and ending with current
date, with at least 15 dates in list - but only from year in cell A2.
Of-course you can define the dates list in your own way).
Format the range C2:C46 in some date format.
Define a named range Days
=OFFSET(Dates!$C$2,,,COUNTIF(Dates!$C:$C,">0"),1)
E1='Months'
E2=IF(DATE($A$2,ROW()-1,1)>TODAY(),"",DATE($A$2;ROW()-1,1))
Copy the formula down to E13, and format the range E2:E13 as Custom "mmmm"
Define a named range Months
=OFFSET(Dates!$E$2,,,COUNTIF(Dates!$E:$E,">0"),1)
Create a sheet Production
A1
1 enter headers (Date, Worker, Model, Quantity)
Select cell A2, from menu select Data.Validation.List and into Source field
enter
=Days
Format A2 as date
Select B2. Again Data.Validation.List, with Source=Workers
Select C2. Data.Validation.List with Source=Models
Copy A2:C2 down for as many rows as you need.
Define named ranges
ProdDate=OFFSET(Production!$A$2,,,COUNTIF(Production!$A:$A,">0"),1)
ProdWorker=OFFSET(Production!$B$2,,,COUNTIF(Production!$A:$A,">0"),1)
ProdModel=OFFSET(Production!$C$2,,,COUNTIF(Production!$A:$A,">0"),1)
ProdQty=OFFSET(Production!$D$2,,,COUNTIF(Production!$A:$A,">0"),1)
Here you enter all your production data. You can use autofilter to hide
filled rows when entering new data.
On all sheets, avoid empty rows. Whenever you need to delete some entry,
delete the entire row only.
Now you need various report sheets. A couple of examples.
Create a sheet DailyRep
A1='Date:'
A2 - create a data validation list with source=Days and format as date
A3='Worker'
B3=IF(COLUMN()-1<=SUMPRODUCT(--(Models<>"")),INDEX(Models,COLUMN()-1,1),"")
and copy to right so all your models are displayed.
A4=IF(ISERROR(Workers),"",Workers)
and copy down so all your workers are displayed.
B4=IF(OR($A4="",B$3=""),"",SUMPRODUCT(--(ProdDate=$A$2),--(ProdWorker=$A4),-
-(ProdModel=B$3),--(ProdQty)))
and copy it into table determined by workers and models headers.
Now when you select a date from list, all production data for this day are
displayed.
(Maybe you need to switch row and column layout in this report - and maybe
you prefer to display nothing when quantity=0. Also you can add summary
row/column, etc.)
To create a monthly report like dayly above, create a copy of DailyRep, and
rename it as MonthlyRep.
A1='Month:'
A2 - data validation lists source=Months, and format as Custom "mmmm"
B4=IF(OR($A4="";B$3=""),"",SUMPRODUCT(--(MONTH(ProdDate)=MONTH($A$2)),--(Pro
dWorker=$A4),--(ProdModel=B$3),--(ProdQty)))
and copy it into table determined by workers and models headers.
You can have any number of report sheets, p.e. you can have a report where
you select a worker, and get listed all models by dates, he produced in year
or month {for such report you'll need an additional column (can be hidden)
in production sheet} and so on.
))