N
NDBC
A lot of you have been helping me out over the last week with my scoring
program. I have got a basic spreadsheet that works now but is slow to operate
because of the number of formulas that are in operation. I am trying to make
things more efficient with a form and a bit of code.
What I have at the moment is 7 classes of riders with 50 teams in each class
and 20 laps per team. I have a running sheet on the front page where you type
in the riders number and the time gets automatically stored in the cell
beside the rider number (code with the now function). I use countif on the
rider numbers to count what lap it is and then I create a cell that is the
combination of the rider number and lap so I can do a single vlookup on the
data. eg. If it is rider 100 and his lap 2 the number that represents this is
100.02 (100+lap/100).
So sheet looks like this
Rider Time Lap ID No.
100 7:34:23 1 100.01
100 7:50:26 2 100.02
I then have a worksheet for each class where the lap times get sorted.It is
in the format
Rider Lap1 Lap2 Lap3 Lap4 .......Lap20
100
101
102
......
150
As you can imagine there 50x20 vlookups for each class or 7000 vlookups in
total happening with every press of enter.
It works but probably won't be too good on an old computer.
My latest idea is to use this form to get the data and store it in the right
cell straight up. That way there are only a few calcs at any one time and the
number of riders/laps/classes can be unlimited without using much more
computer power.
I think I can write the code for the form (well probably will need some
assistance on a few points) but my concern is this.
I would like to set it up so you just type rider number in the form press
enter and the data is stored and the form resets to blank ready for the next
rider number. My problem is how do I handle fixing up typos in the rider
number. The average user is not going to be an excel expert and will not
necessarily be familiar with the spreadsheet layout. They need to be able to
fix them on the run.
My thoughts were to have say 5 text boxes on the form and every time you
press enter the rider numbers move down the boxes one. This would mean you
have 5 chances to fix it before it disappears off the screen. To do this the
time would have to stay associated with the rider number in the box and move
down with it and only get saved to the spreadsheet after it goes from box 5.
Can anyone think of an easier way or is the code to do the above simpler
than I think. I figure it is better to have the right plan before I start
than start and change plans (again).
Sorry for the very long story and thank you.
program. I have got a basic spreadsheet that works now but is slow to operate
because of the number of formulas that are in operation. I am trying to make
things more efficient with a form and a bit of code.
What I have at the moment is 7 classes of riders with 50 teams in each class
and 20 laps per team. I have a running sheet on the front page where you type
in the riders number and the time gets automatically stored in the cell
beside the rider number (code with the now function). I use countif on the
rider numbers to count what lap it is and then I create a cell that is the
combination of the rider number and lap so I can do a single vlookup on the
data. eg. If it is rider 100 and his lap 2 the number that represents this is
100.02 (100+lap/100).
So sheet looks like this
Rider Time Lap ID No.
100 7:34:23 1 100.01
100 7:50:26 2 100.02
I then have a worksheet for each class where the lap times get sorted.It is
in the format
Rider Lap1 Lap2 Lap3 Lap4 .......Lap20
100
101
102
......
150
As you can imagine there 50x20 vlookups for each class or 7000 vlookups in
total happening with every press of enter.
It works but probably won't be too good on an old computer.
My latest idea is to use this form to get the data and store it in the right
cell straight up. That way there are only a few calcs at any one time and the
number of riders/laps/classes can be unlimited without using much more
computer power.
I think I can write the code for the form (well probably will need some
assistance on a few points) but my concern is this.
I would like to set it up so you just type rider number in the form press
enter and the data is stored and the form resets to blank ready for the next
rider number. My problem is how do I handle fixing up typos in the rider
number. The average user is not going to be an excel expert and will not
necessarily be familiar with the spreadsheet layout. They need to be able to
fix them on the run.
My thoughts were to have say 5 text boxes on the form and every time you
press enter the rider numbers move down the boxes one. This would mean you
have 5 chances to fix it before it disappears off the screen. To do this the
time would have to stay associated with the rider number in the box and move
down with it and only get saved to the spreadsheet after it goes from box 5.
Can anyone think of an easier way or is the code to do the above simpler
than I think. I figure it is better to have the right plan before I start
than start and change plans (again).
Sorry for the very long story and thank you.