Hi
Here is an example on fly
Start with new workbook. Create sheet SetUp.
A1="Season:"
B1 - enter the year number
Select B1, and define it as a name Season (From menu: Insert>Name>Define,
etc.)
On all other sheets except reports, I assume the 1st row is for headers. And
there must not be any gaps (rows without any data) in tables
Create sheet Places with columns Race, Date, ... ;
Create a named range Places as
=OFFSET(Places!$A$1,1,,COUNTA(Places!$A:$A)-1,1)
When you want to get any additional information from Places sheet p.e. for
reports, then you can define also a named range p.e. PlacesTbl
=OFFSET(Places!$A$1,1,,COUNTA(Places!$A:$A)-1,#)
where # is a number of columns in Places table;
Select some reasonable range in column Dates, starting from cell B2, select
from Data menu Validation, and set for selected range the validation to
Custom with formula
=(YEAR(B2)=Season)
NB! Relative reference! You also can determine error alert text there.
From now on, you can enter into table only dates for year, determined as
Season on SetUp sheet.
Enter all information into table.
Create sheet Teams with columns Team, TotalPoints, Rank
Create a named range Teams as
=OFFSET(Teams!$A$1,1,,COUNTA(Teams!$A:$A)-1,1)
Enter team names into Team column. Other columns must contain formulas
(about them somewhat later).
Create sheet Pilots with columns Pilot, Team, TotalPoints, Rank
Create a named range Pilots as
=OFFSET(Pilots!$A$1,1,,COUNTA(Pilots!$A:$A)-1,1)
Select some reasonable amount of rows in Team column (starting from B2),
from Data menu select Validation, and set for selected range the validation
to list with source
=Teams
From now on, you can enter only teams present in Teams table. And you can
select those teams from drop-down list.
Enter pilot information (name and team) into table. To columns Points and
Rank we'll return later.
Create a sheet Races with columns Race, Pilot, Result, Rank, Points.
There is a problem in Excel with entering times exacter as 1 second - you
can format the cell to display p.e. hunderths of second, but I havn't found
any way to enter them except by formula. So in my example I added 2 columns,
and the table will be Race, Pilot, Result0, Result1, Result, Place, Points.
Format some reasonable amount of rows in Race column as data validation list
with source
=Races
Format same number of rows in Pilot column as data validation list with
source
=Pilots
Format same number of rows in Result0 column as Custom "hh:mm:ss" - you
enter here race results to seconds.
Format same number of rows in Result1 column as General - you enter
hunderths of seconds here
Into cell E2 (in Results column) enter the formula
=IF(C2="","",C2+D2/8640000)
, format it as Custom "hh:mm:ss.00", and copy down for same amount of rows
as formatted previous columns.
Define named range Race
=OFFSET(Races!$A$1,1,,COUNTA(Races!$A:$A)-1,1)
Select any cell in row 2, and define a named range ResultArray
=OFFSET(Races!$E$1,MATCH(Races!$A2,Race,0),,COUNTIF(Race,Races!$A2),1)
Into cell F2 (Rank) enter the formula
=IF(E2="","",RANK(E2;ResultArray;1))
, and copy down for same amount of rows as previous columns.
Into cell G2 (Points) enter the formula
=IF(F2="","",10-F2+(F2=1))
, and copy down for same amount of rows as previous columns.
Enter all available race data into table.
NB! The dable must be sorted by races - otherwise formulas don't work.
Create a sheet RaceTable (It is a report sheet really)
Into cell A2 enter the formula
=IF(ISERROR(INDEX(Pilots,ROW()-1,1)),"",INDEX(Pilots,ROW()-1,1))
and copy down for some reasonable amount of rows.
Into cell B1 enter the formula
=IF(ISERROR(INDEX(Places,COLUMN()-1,1)),"",INDEX(Places,COLUMN()-1,1))
and copy right for some reasonable amount of columns.
Select cell B2, and define the named range NamesArray
=OFFSET(Races!$B$1,MATCH(RaceTable!B$1,Race,0),,COUNTIF(Race,RaceTable!B$1),1)
Define the named range PointsArray
=OFFSET(Races!$G$1,MATCH(RaceTable!B$1,Race,0),,COUNTIF(Race,RaceTable!B$1),1)
Enter into B2 the formula
=IF(OR(B$1="",$A2="",ISERROR(INDEX(NamesArray,ROW()-1,0))),"",SUMIF(NamesArray,$A2,PointsArray))
, and copy it to range covering same number of rows and columns, as formulas
in 1st row and column.
Select the sheet Pilots, and there the cell C2 (TotalPoints).
Define the named range RaceTable
=OFFSET(RaceTable!$A$1,MATCH(Pilots!A2,Pilots,0),1,,COUNTA(Places))
Into C2 enter the formula
=IF(A2="","",SUM(RaceTable))
, and copy down for same number of rows as you have data validation for Team
column.
Define the named range PilotsPts
=OFFSET(Pilots!$C$1,1,,COUNT(Pilots!$C:$C),1)
Into cell D2, enter the formula
=IF(C2="","",RANK(C2,PilotsPts,0))
, and copy down for same number of rows as the formula in previous column.
I think for Teams sheet, you knoe now enough to create formulas for teams
TotalPoints and Rank yourself.
Probably you want report sheets to get race/season reports. Season report
will be fixed, probably you´ll get most of data from RaceTable, and from
Pilots and Teams tables. For race report is my advice to have a cell vith
data validation list to select the race, and formulas will retrieve all info
for this selected race. I think you are capable to create those report
sheets yourself too.
With new season started, save the workbook under new name (p.e. old name was
CurrentSeason.xls, save it as Season2005.xls to keep archive), open the old
workbook again, enter the new season into SetUp sheet, clear (Clear
Contents, NOT Delete) all non-formula data from Races table, correct Pilots,
Teams and Places tables when needed, and you are ready for new season.
