Hi
An example:
Create a workbook with 3 sheets - Sales, Names and Report
Into Names worksheet enter the list of names, with heading in A1
Define the named range Name
=OFFSET(Names!$A$2,,,COUNTIF(Names!$A:$A,"<>")-1,1)
On sheet Sales create a table with columns
ActiveRow, RowRank, Name, Date, Sales
When you want, then you can format some range in column C (Name), using
Data.Validation.List (=Name), so you can select names from dropdown.
Define named range Sales
=OFFSET(Sales!$B$2,,,COUNTIF(Sales!$C:$C,">""")-1,4)
On sheet Report into range A1:A3 enter: "Name:", "Month:", "Year:"
Format cell B1 using Data.Validation.List (=Name)
Format cell B2 using Data.Validation.List
("January","February",...,"December")
NB! Fill the gap in list above, so all 12 months are included!
Format cell B3 using Data.Validation.List (2003,2004)
Define named range RepName
=Report!$B$1
Define named range RepMonth
=IF(Report!$B$2="",0,MATCH(Report!$B$2,{"January","February",...,"December"}
;0))
NB! Fill the gap in formula above, so all 12 months are included!
Define named range RepYear
=Report!$B$3
On sheet Sales, select any cell in row 2 and create named range Selected
=IF(OR(OR(Sales!$C2="",Sales!$C2<>RepName),AND(RepYear<>"",YEAR(Sales!$D2)<>
RepYear),AND(RepMonth>0,MONTH(Sales!$D2)<>RepMonth)),"",ROW())
Into A2 enter the formula
=Selected
Into B2 enter the formula
=IF(A2="","",RANK(A2,$A:$A,1))
Copy the range A2:B2 down as much rows as you think you'll need
Hide columns A:B
Enter your sales table, when it didn't exist earlier.
On sheet Report, into some row, p.e. row 5, enter header row, p.e. Nr, Date,
Sales
Into cell A6 enter the formula
=IF(ISERROR(VLOOKUP(ROW(A6)-ROW(A$5),Sales,1,FALSE)),"",VLOOKUP(ROW(A6)-ROW(
A$5),Sales,1,FALSE))
Into B6
=IF($A6="","",VLOOKUP($A6,Sales,3,FALSE))
Into C6
=IF($A6="","",VLOOKUP($A6,Sales,4,FALSE))
Copy range A6:C6 down as much as you think you'll need.
Create a total field for selected sales on passable location.
It's done!
