OVER 2 YOU, 013 (18/01/01)

GOLF SOLUTIONS

Every year a group of 12 of us have a week playing play golf. We play in fours (3 groups of 4 each day) for 5 days. Ideally we would like to get it so that everybody plays with (within the group of) everybody else. No matter how we arrange it we cannot achieve this goal. Does anybody know a program or a formula (using Excel) to achieve our aim and make 12 old (ish) men very happy?

Robin Dent

You must be non-bridge playing golfers or else you would have come across a 3-table (12 player) Individual Movement allowing each bridge player to play a number of hands with each other player.  Extend your golfing holiday by half a day and then play nine holes in partnership, over 11 half rounds, with each other player and play in the same group as every of player at least twice.

Tony Philpott,

TICKET PRINTING

I help to run a village music society and am looking for a software application that would enable me to print entrance tickets that automatically number each ticket from zero to 300.

John Bartlett,

Although word-processing applications can be used to produce and number tickets, using the page numbering feature, an ideal system is Labels Unlimited from The Learning Co (UK) Ltd of Tilgate Forest Business Park, Brighton Road, Crawley, RH11 9BP.

It can be set up to print labels or tickets of any size, with serial numbers if desired, and even barcodes.   Text can be repeated on each label/ticket and/or imported from a simple database, and address labels and receipts can be generated, which is useful for a small society.

David Lyall, Cheltenham

From time to time we have had to produce numbered tickets on occasions (mercifully rare nowadays) when putting on self-promoted concerts. We found the following technique worked well and has the advantage of not requiring any specialised software.  You probably already use a mail merge procedure to label envelopes when keeping in touch with the members of your music society.  The ticket creation is simply an adaptation of this procedure.

Firstly, you need to buy some blank business cards (you can get these from PC World and other places).  This will give you 10 tickets per A4 sheet.

Secondly, you need to make a data file with the ticket numbers in. This is a bit tedious but you only ever have to do it once and, in your case, go up to 300. We find that a text file works well and is nice and simple. So for example if you are making an ASCII delimited data file you open a new text file. The first line is where you declare the names of your data fields.  In this case you only have one data field - the ticket number.  So the first line should read (including the inverted commas):

"Ticket number"

The next line is where the data start.  It is all the same to the computer if you put:

1,2,3,4,5,6, ... 300 or

1,

2,

3,

4,

5,

6

...

300

We prefer the latter just because we can see more easily if we have missed something out.

Save this text file as something obvious like Ticket numbers.txt. Then select an appropriate label format on your word processor (eg 2 x 5 for business cards) and do a mail merge with Ticket numbers.txt.  If you use the above format then you are using an ASCII delimited format with comma delimiters and your description file is the first line of the data file (this means that a separate description file is NOT necessary). Your software will probably interrogate you on all these matters.

As with any merge you can type in the invariant details - in this case the concert place, date and time - and insert the data field required anywhere you like to have it on the ticket. We always view before printing just to check that the software is behaving itself. As an alternative to buying business card blanks, if you do not mind cutting up the paper, you can use a different label format that gets more tickets out of a sheet. This is cheaper but, frankly, the business card blanks do not cost that much and they feel a lot more professional.

Jonathan Darnborough and Claire-Louise Lucas,

It is easy. I print out numbered tickets for our concerts. I use mail merge and then guillotine A4 into eight A7 (that's half three times).

The best for generating number sequences is undoubtedly a spreadsheet, therefore I use Excel for the database file. I use Publisher for the tickets themselves. It is easiest to set up a divided sheet, but I'm sure Word, or even Excel, could do as well.

Set up your blank page in your chosen application. Design the fixed text in one of the A7 cells.

Create an Excel file in the first 8 columns create your number headings say T1..T8, then in subsequent columns create headings that will vary from event to event - date etc. You can format date in a wide range of styles in your Excel cell but this will only print in a very basic format on the ticket so split the date into day, month, year etc. For the numbering you can format Excel to give leading zeroes to small numbers but as with date this formatting does not come through the merge so I start at 100. In row 2 put 100 101 in the first two columns, high light these two then drag the + in bottom rh corner to fill the remaining 6 columns. I row 3 put 108 109 and fill likewise. Now highlight 100 & 108 and drag down to the number you require. Complete the table and you have an error free sequence. For the subsequent text (row 3 +) rows Column I onwards. Just type = and click the cell above <return> and "block" copy this to fill the rest of the table. It sounds a lot but it does not take long, you now have a master file, all you have to do for each event is to enter the text in row 2. (You will be asked later for the sheet name so call it something simple by double clicking on the tab.)

Set up mail merge to use Excel files. Now using the mail merge insert the text fields but leave out the numbers for the moment. Copy the sample ticket grouping if necessary and paste it to the remaining seven blanks.

Temporarily number each ticket on the sheet A-H say. Print out a trail sheet and cut it up systematically as you would for the production run. You will have a pile of eight but not in order. If ticket G is on the top of the pile replace this by the T1 field there and so on through the pile. You will now have a sheet that produces a pile in order.

Print out a couple of sheets to make sure everything is as you want it. It takes a few moments to set up but your 300 will all be the same. Not mine, but I have seen someone find half the tickets had a previous date when he was handing them out. Yours will all be the same!

As a bit of gilt to the gingerbread you can create further columns to overprint some tickets with say "Complimentary" for the local paper critic etc.

Ted Rodgers, 

LOSS ADJUSTERS

Following a disastrous loss on one of our Investment Club share holdings I was asked to devise a Stop Loss Program covering the twenty equities held.  I have devised an Excel program that works quite well but is time consuming, requiring manual loading of maximum prices each share has reached to date and daily loading of all the latest prices. 

Is there a web page or software program that will do all the necessary calculations, highlighting any shares that have from their maximum price achieved since the date of purchase?

Mike Pattison,

I have devised a very simple procedure using Lotus 123, which is automatic once the file is set up. Say you have the mid market price on Line 9 of your spreadsheet starting in cell B9 and running along to cell IV9and the stop loss from B11…

In say B15 write .825 (i.e.17.5% stop loss)

In B10 write @max($B$9..b9)*$b$15

Copy this across the spread sheet.  On a daily basis you can get a year-long file on one sheet. You can then graph this out, to include mmp, purchase price, stop loss, moving average. I also have a month-end table which also includes commission and other charges. This is a two-page document covering the shares now sold and the current holding.  The sums are done automatically simply by feeding in the relevant mmp for each share. I also include at the bottom, the summary of all dividends received. (I have a separate dividend file for each tax year)

It really does not take very long to do either my daily input or the monthly input.

Alan Thomas

POWER SAVERS
Has anyone written an Excel spreadsheet to calculate which of the power companies will give the best deal for your area. What is required is a method of entering the number of units of gas and electricity used again the cost per unit of each supplier. This information is obtainable from the Ofgem.gov.uk web side. Standing order charges and direct debit discount also require taking into account
Peter Martin,

We have a website at www.energysaveruk.com which will compare all suppliers tariffs in all regions of the country, in a matter of seconds. There is also a host of other helpful information for consumers to view.
Charlie Armour,

CAN YOU HELP?

I am a member of the Vintage Motor Cycle Club. The Club is divided into many Sections, each organising vintage runs. Hundreds of different runs are arranged each year. Each rider is supplied with a route at the beginning of each ride. This is carried in a handlebar holder, which can be turned and read as the route progresses.  An ideal route is a strip map, showing road numbers, progressive and total mileage, left and right turns, or straight on; position of road signs, steep hills, petrol stations and check points. It is important that a route has clarity and can be read whilst riding. The Club has no standard format for these routes and maps and needs one. All suggestions gratefully received.

Kenneth Peterkin,

Could anyone recommend software, which creates random numbers? I want to use such a program when I prepare the weekly draw sheet for the Open Bowling Competition, which I run at my local bowling club. I want to number the competitors 1 to 16 and then wait for the computer to churn out the eight matches - then no-one can accuse me of match-fixing!

Dan Blunt,

I am trying to find a software package that would help to monitor the holiday arrangements of a team of around 30 employees - this would show number of days allocated/taken/outstanding, as well as show the state-of-play at any one time in terms of who's already on leave.  Ideally, the system could be used via Lotus Notes to enable employees to apply for their leave with permission resting with the Admin Manager.

Mick O'Rourke,