Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 09, 01:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 204
Default Form design question.

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.

Sorry for the very long story and thank you.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Order form design Learner101b Excel Discussion (Misc queries) 3 December 18th 07 11:51 PM
how to design a form Allan Collings New Users to Excel 1 July 17th 06 12:47 PM
Design a form Cyrus New Users to Excel 2 May 9th 06 06:43 AM
Design complicated form JMJJ Cates New Users to Excel 3 October 16th 05 12:02 AM
design form? [email protected] New Users to Excel 5 January 31st 05 04:26 AM


All times are GMT +1. The time now is 05:44 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017