how to start a database

[adminsly]

hi guys, i want to start a database on excel 2000, i never had to do this before, and i know this can be done.
i just dont know where to start from !

what i want to do is, it is a racing club, i want a listing of all pilot and a sheet where we can enter the number of the car(all pilot info need will be transfer) for final result of the race.(right now they enter the car no, name , city at every race at every registration, if he is register to 3 race they do the process 3 time.)

like in access went we create the database and after the query, that what i want to do in excel.

any tip or link will be apreciate

thanks

sly

[Arvi Laanemets]

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.

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"adminsly" <[email protected]> wrote in
message news:[email protected]...
>
> hi guys, i want to start a database on excel 2000, i never had to do
> this before, and i know this can be done.
> i just dont know where to start from !
>
> what i want to do is, it is a racing club, i want a listing of all
> pilot and a sheet where we can enter the number of the car(all pilot
> info need will be transfer) for final result of the race.(right now
> they enter the car no, name , city at every race at every registration,
> if he is register to 3 race they do the process 3 time.)
>
> like in access went we create the database and after the query, that
> what i want to do in excel.
>
> any tip or link will be apreciate
>
> thanks
>
> sly
>
>
> --
> adminsly
> ------------------------------------------------------------------------
> adminsly's Profile:
> http://www.excelforum.com/member.php...o&userid=26571
> View this thread: http://www.excelforum.com/showthread...hreadid=398458
>

[adminsly]

arvi, first of all thanks that much more than i expect !! you really seem to know what i want ;-)

but to start i dont want that much and to be honest i find it quit hard to follow, but i am sure it work.

if you want i will attach my file demo or try out. just nename it .xls

i have 2 sheet, one with pilote info and 2 with race result
i want in sheet race result to just enter pilote NUMBER and this will bring me
the pilote number, fist name , last name, city

if this is possible ? send it back to me
i am better when i see it.


thanks

[adminsly]

arvi , i am trying to do the file you explain,
when you say
Create a named range Places as
=OFFSET(Places!$A$1,1,,COUNTA(Places!$A:$A)-1,1)

where do i creat a named place ?
where do i place that in the sheet ?

[Arvi Laanemets]

Hi

To create a nane/named range, select from Edit menu Insert>Name>Define.
Determine the name for range, and the formula/reference, and click Add.

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"adminsly" <[email protected]> wrote in
message news:[email protected]...
>
> arvi , i am trying to do the file you explain,
> when you say
> Create a named range Places as
> =OFFSET(Places!$A$1,1,,COUNTA(Places!$A:$A)-1,1)
>
> where do i creat a named place ?
> where do i place that in the sheet ?
>
>
> --
> adminsly
> ------------------------------------------------------------------------
> adminsly's Profile:
> http://www.excelforum.com/member.php...o&userid=26571
> View this thread: http://www.excelforum.com/showthread...hreadid=398458
>