how to start a database

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?action=getinfo&userid=26571
View this thread: http://www.excelforum.com/showthread.php?threadid=398458

0
8/24/2005 1:45:10 AM
excel 39879 articles. 2 followers. Follow

4 Replies
718 Views

Similar Articles

[PageSpeed] 1

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" <adminsly.1u8xib_1124849104.8032@excelforum-nospam.com> wrote in 
message news:adminsly.1u8xib_1124849104.8032@excelforum-nospam.com...
>
> 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?action=getinfo&userid=26571
> View this thread: http://www.excelforum.com/showthread.php?threadid=398458
> 


0
garbage (650)
8/24/2005 9:32:52 AM
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


+-------------------------------------------------------------------+
|Filename: asmdemo1.zip                                             |
|Download: http://www.excelforum.com/attachment.php?postid=3746     |
+-------------------------------------------------------------------+

-- 
adminsly
------------------------------------------------------------------------
adminsly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26571
View this thread: http://www.excelforum.com/showthread.php?threadid=398458

0
8/26/2005 2:10:02 AM
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?action=getinfo&userid=26571
View this thread: http://www.excelforum.com/showthread.php?threadid=398458

0
8/26/2005 2:30:06 AM
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" <adminsly.1ucpmc_1125025503.4907@excelforum-nospam.com> wrote in 
message news:adminsly.1ucpmc_1125025503.4907@excelforum-nospam.com...
>
> 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?action=getinfo&userid=26571
> View this thread: http://www.excelforum.com/showthread.php?threadid=398458
> 


0
garbage (650)
8/26/2005 5:55:20 AM
Reply:

Similar Artilces:

Starting up on start-up help!
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello all, <br><br>I'm new here so Hello. I just have a question to see if anyone can help here. I just recently bought a new MacBook Pro, and I got the Microsoft Office for it too. Well, I recently noticed that on start up, entourages My Day keeps opening up. Well when I right click the menu, I check off the box where it says to start on login. <br><br>Well here is the problem. Everytime I uncheck it, it still keeps opening up on start up. <br><br>Anyone know why?...

Need advice to start off a project
Hello! I have never really used Excel much but I have had an idea which could make some of the work I do a lot easier. I know Excel is powerful but I don't know the best way to approach it so can anyone out there help me? I make visits to places and record what kind of visit it was on a spreadsheet with the values in each cell being the date. At the bottom there is a summary box which shows, on each date, how many visits I made of each type. At the moment this is done manually but I am sure it can be done automatically only how? On a different sheet I would then like to have a summary of...

Cannot start Outlook 2002
My computer is usualy connected to an MS Exchange server at work. However I'd like to use Outlook at home with another e-mail account. When I try to start Outlook I get an error message : Cannot connect to exchange server. So I click work offline. Then, get another error message: Cannot start Outlook. Could not open information store. The the program quits. Anyone know a work around? Thanks. Create a new profile for your home account. Then set Outlook to prompt for profile. Select your home profile and Outlook will not try to connect to Exchange. --� Milly Staples [MVP - Outlook...

Exchange Services Do not Start After a Restart #2
I have a SBS 2003 Running and for some reason some of my Exchange Service do not start after a restart. System Attendand tries to start shuts down unexpectedly. I can go to services right after logging in and start the services and they start with no problem. Right before the error that service did not start, I get this warning: "A service process other than the one launched by the Service Control Manager connected when starting the Microsoft Exchange System Attendant service. The Service Control Manager launched process 2448 and process 3688 connected instead. Note that if this se...

User ID and Logon for Database
I am thinking of changing a very old program so that the user now has to enter a user ID and password. My VB skills are really beginner level. Can someone suggest a good article on how to do this, preferably with code samples for VB2005? -- Bettie "BGCSOCAL" <BGCSOCAL@discussions.microsoft.com> wrote in message news:B296D2F7-3824-4914-BDF3-85847F8027E0@microsoft.com... >I am thinking of changing a very old program so that the user now has to > enter a user ID and password. My VB skills are really beginner level. Can > someone suggest a good article o...

Macro or Lookup? Not sure where to start.
I have created two worksheets: 1) Auction Items Item# Table# Location# ItemDescription WinBid are the headers of the data. 2) Bidders Bidder# FirstName LastName I would like to be able to create a macro or something that will allow me to enter the item#, bidder# and winning bid price and have a sheet filled with. ie. Item# 2 (Handcrafted Table) Bidder# 12 (John Doe) Winning Bid $250 Can excel pull from two sheets and created another sheet with all the final details? I am a little new to excel (version 2000) it is the only tool/application available to us at this time. If anyone can poi...

Problem with scroll bars on Database window?
If I open say a form from the database window, amend it and then save it when I try to scroll the database window the vertical scroll bar doesn't move I have to click on say Reports then go back to Forms and then it moves. The same thing happens in any of the objects in the Database window, if I open close a report the same thing happens. Has anyone else experienced this problem? Tony I've noticed it with several different versions of both Access and Excel. In my case, I just move the window slightly using the mouse and the database becomes responsive again. I think this is m...

Outlook starting problem
"Cannot start Microsoft Outlook. A dialog box is open. Close it and try again." ...

Autoclose Access Database if not using for more than a certain period
My Access database is working in a multi-user environment. Is there any function to close the access database if the user is not using the file for more than 2 hours? Thanks. See the following: http://support.microsoft.com/kb/210297/en-us or another option would be: http://support.microsoft.com/kb/304408 <xiaodan86@hotmail.com> wrote in message news:1178590698.082941.302910@e65g2000hsc.googlegroups.com... > My Access database is working in a multi-user environment. > Is there any function to close the access database if the user is not > using the file for more than 2 ho...

Running CHKDSK at start up
Is there a way to run chkdsk f/r on all five drives on one PC at start up? PC runs Windows XP Pro SP3. This way all drives would be checked and errors fixed, if necessary, in one go, at start up on this machine. We intend to do this on this heavily used PC from time to time - we do not need the desired chkdsk functionality to kick in at every boot! Regards and TIA. Avatar -------- "Avatar" <Avatar@re.birth> wrote in message news:hrb4n2$53i$1@news.eternal-september.org... > Is there a way to run chkdsk f/r on all five drives on one PC at start up? ...

How do I add consecutive numbers but start over at a break in the.
add consecutive numbers but start over at a break in them. I'm trying to calculate consecutive days of attendance. ...

Starting a new paragraph within a cell
How do you create a line break within a cell? For example, if wanting to list 2 or more items it looks better if each one is printed under the other as a list. Or in a comment column when needing to start a new paragraph, again within the cell. Hi. You can press Alt + Enter to create a line break within a cell. Kath firman wrote: > How do you create a line break within a cell? For example, if wanting to list > 2 or more items it looks better if each one is printed under the other as a > list. Or in a comment column when needing to start a new paragraph, again > within th...

Can't Start Any Program
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel It has been two weeks since I can open an Office Program. Please HEEELP! This is my word error report: Microsoft Error Reporting log version: 2.0 Error Signature: Exception: EXC_BAD_ACCESS Date/Time: 2009-09-09 19:04:02 -0400 Application Name: Microsoft Word Application Bundle ID: com.microsoft.Word Application Signature: MSWD Application Version: 12.2.0.090605 Crashed Module Name: CoreFoundation Crashed Module Version: unknown Crashed Module Offset: 0x00075354 Blame Module Name: CoreFoundation Blame Module Version: unk...

Length of time between 2 database entries
Helpdesk calls are logged in 2 tables, 'Calls' with 1 row per call, and 'CallActions' with a variable number of entries per call log. The schema for CallActions is: CallID, ActionID, datestamp, narrative The Narrative can be made up of free-form text or just contain specific text to show something has happened to the call, e.g. '**new', '**open', '**investigating' etc. The CallID is unique in the 'Calls' table, the ActionID is unique in the 'CallActions' table. I need to pick out both '**New' and the first '**Open...

CD does not start
After installing XP on a new disk everything appeared normal. I was able to install a few applications from their cds. Then I attempted to install an application from a downloaded file, but it did not work. So I installed MS frame works and windows installer. Then I was able to install the application from the downloaded file. When I attempted to install another application from its cd (with autorun), nothing happened, namely it fail to start. If I force the setup.exe from Explorer, the error message shows up: "the application configuration is incorrect". Thinking for...

Loading Adventure Networks Database fails
I am following the instructions page 331 to load the Adventure Networks database. I am running on an absolutely fresh Win2003 SBS and have followed all the instructions. A couple of problems (referring to the bullets in the manual): 2. My Administrator account has domain and local admininstrator privileges and was used to install MS CRM. MS CRM works perfectly. So far so good. 7. I cannot confirm that the five users were created? Why is that? What should I do about it? 10. After entering URL I get an error: That I do not have a MS CRM license??? Help, - Jakob Jakob, ...

Money 2007 tries to update on each start
On each start up, Money 2007 advises that in order to run properly an update to Money 2007 is necessary. When I click OK it downloads files and again I click to start the upgrade. After a few minutes the update completes and a dialog box advises that I should restart Money and if it will not open properly I should restart my computer. I do so and all seems to be OK, but on starting money the update dialog box is back. I checked the Money folder and the update log shows that the update completed. So how do I stop the annoying update dialog box from displaying on each start? "GWL&qu...

Importing Items from an Access 2003 database to tasks
I want to import a table from access into outlook's task folder. I have a sales group of people that need to retrieve items from this database and add it to their tasks. I would like this to be automated to kick off every two hours. Can this be done? Does anyone have any code as an example for this? I am an experienced VB programmer (.net and classic) but have done very little programming in Outlook itself. The program needs to be contained within Outlook. Any code, or examples would be great. John Wright ...

What is the main thing to remember to get started in EXCEL?
What are some tips to make Excel easier to understand and employ? Just remember that Excel is not a database, though it can do some database functions, and it is not a word processor, though it can do some word processing functions. Get a good book - one by John Walkenbach would be a good choice: http://www.j-walk.com/ss/books/index.htm HTH, Bernie MS Excel MVP "Mr.Harrison" <Mr.Harrison@discussions.microsoft.com> wrote in message news:3796CE4E-1D28-4BDD-9D8F-E378C10F892F@microsoft.com... > What are some tips to make Excel easier to understand and employ? if you spen...

Outlook 2007 won't start, message, can't start microsoft office o.
I run Windows 7 and Outlook 2007 just quit working. The error message is: Cannot start Microsoft Office Outlook. Cannot open the Outlook window. I have had a similar problem because I keep my pst file on a network share so I can use it with my roaming profile, and sometimes the network share goes awol.... To reset the outlook window run 'outlook.exe /resetnavpane' at the command prompt. "Tony Wybrow" wrote: > I run Windows 7 and Outlook 2007 just quit working. The error message is: > Cannot start Microsoft Office Outlook. Cannot open the Outlook w...

Multiple users of one shared database on 2003 and 2007
Hi Our office recently upgraded 50% of our machines to office 2007, unfortunately as 50% are still running office 2003 we are having issues with functionality in the shared database (created in 2003) the command buttons created using wizards in 2003 are not working in 2007, in addition to this i have the added risk of 2007 users potentially saving the file in 2007 format, making is inaccessible to the 2003 users. I understand that I can split the database and create a new interface in 2007 for the 2007 users, (fixing all of the buttons) however am I able to link front ends from 2 versions to...

Exchange Database question
Hi All, I restored a online backup of my priv.edb on a second server to test with a third party tool for mailbox recovery, namely powertools by ontrack. Of course since it is an online backup the database file itself is locked. I haven't got the time to build a test exchange machine and just want to test against the restored file. I there any way to get the lock of the database file? On Tue, 11 Jan 2005 00:49:01 -0800, "Gawin" <McArcher@@hotmail.com> wrote: >Hi All, > >I restored a online backup of my priv.edb on a second server to test with a >third p...

How do I break a link to an external database but keep table data?
I am trying to break the link to an external database while keeping all the data in the tables housed locally. It is possible I will loose access to the external database due to updates that will be made. In order for me to work efficiently, I will need to house all the data locally and manually update all the information. (Currentlt the external database is updated automatically and instantly.) Any ideas, hints, advice? I am using Microsoft Access 2000. I have been looking online and trying to break links but nothing seems to work. The database we use now (locally) is so big it ...

Publisher won't start from Start menu
Its happened before and I can't remember how it was put right (by a nice lady on here if I recall) but I double click my shortcut for Publisher in the Start menu, and...nothing. I have to go into C:\Program Files\office to click on the thumbnail thedre before it'll work. Anyone know how to fix this ? Right click MSPub.exe, Drag the Publisher icon from the Program Files to the desktop or Quick Launch, click "Create shortcut here". -- Mary Sauer http://msauer.mvps.org/ "Phil K" <philk001@gmail.com> wrote in message news:33526F5B-7319-4771-8E3D-4E937F...

2000 database keeps growing 2003 db shuts down
how annoying yesterday was. my gorgeous new 2003 decided its database was big enough and shut down Exchange. I defragged it in the middle of the day and it resumed. very embarassing. I was not expecting that. My 2000 server grows and grows, even over the recomended size limit. it may be slow but its dependable. can I adjust this feature in Exchange 2003 so it does not shut down when the database is over 18 GB? http://support.microsoft.com/?id=828070 Have you already used this article? There are 2 other ways ... 1. Upgrade Exchange 2003 standard to Exchange 2003 Enterprise 2. Wait fo...