OVER 2 YOU, 133 (03/06/03)

SERIAL NUMBERS

I am looking for a way of producing multiple copies of the same MS Office document (Word/Excel), but each with it's own unique serial number. I can do this in Avery DesignPro but have been unable to find a way of doing it in Office. Can anyone help?
Ian Johnson, via email

I use MS Excel to put a unique number/date at the top of each document.  Choose a cell that is in the position on the page where you want the number to appear. Type the first number/date (which can contain letters). Then in the next cell down, underneath the first, type the next number in the sequence. Highlight both cells by left clicking and holding. Then, using the cell handle pull the cells down the page, the numbers will appear in sequence as far as you want. Return to the top of the document and go to row 2. Insert a page break. Move the cursor to the next row and repeat.

Do this as many times as you require. The number will appear on each page.

I find it simpler to print these pages first, so that the paper becomes numbered or dated sheets.  Any other document can then be printed in the normal way, by running these pages through the printer again.

However with time the sequenced numbers can be cut and pasted down the page, their sequencing remains.

Sue Shaw

The following VBA macro code would solve the problem, saving each copy of the file with the value from 1 to 10 (this value could be increased accordingly)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20/05/2003 by User
'
    Dim myfilename As String
    For i = 1 To 10
    myfilename = i & ".doc"
    ActiveDocument.SaveAs FileName:=myfilename
    Next i
End Sub

Other text could be added to make the filename more meaningful such as
Invoice No in the following example:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20/05/2003 by User
'
    Dim myfilename As String
    For i = 1 To 10
    myfilename = "InvoiceNo" & i & ".doc"
    ActiveDocument.SaveAs FileName:=myfilename
    Next i
End Sub

I'm sure a more complex bit of code could be written depending on the
requirements but this is the simplest I could rattle up!
Gary Birks

OBSCURE FORMAT

Whilst clearing out a deceased relatives house recently I came across a small collection of six ‘CVC’ tapes, apparently containing home video movies. Unfortunately I couldn’t find anything to play them on, does anyone know anything about these tapes and are players still available?

Kay Little, via email

I believe these are Compact Video Cassette tapes, an obsolete and long forgotten video format that came out in the mid 1980s. As I recall it was a joint venture between Fuji and the US film company Technicolor and was in productions for maybe a year or so. Up to that time ‘portable’ video equipment consisted of bulky battery powered VHS video recorders and a separate camera. CVC recorders were about half the size and weight and as I understand it the picture quality was every bit as good. This was just before JVC launched the mini VHS tape system (VHS-C) followed by the even smaller 8mm format from Sony and this led to the development of lightweight one-piece ‘camcorders’. In the face of such strong competition CVC quietly disappeared. The last I heard of it was during the early 1990s when portable CVC machines were being sold off in ‘surplus’ shops in London’s Edgware Road for a couple of hundred pounds. I doubt very much if any players have survived so the tapes are almost certainly unplayable but you might try keeping an eye on the ‘vintage electronics’ sections in the online auction on ebay.

Terry Champion, Letchworth

ATHLETICS RESULTS

I am scoring for an athletics meeting where the athlete’s performances are awarded points from a table. Using the LOOKUP function there is no problem in formulating the points column in an Excel Spreadsheet for Field Events where the points increase with the greater distances. However, I cannot get an accurate result for Track Events where the points are decreased as the time increases. Can anyone tell me how to get it right?

Anna Grinonneau, via email

You have to use negative numbers. In the lookup table put the times as negative values with the slowest at the top and the appropriate number of points for beating that time in the next column. In the points table, either enter the times as negative numbers or - better - have an extra column to multiply the time by -1 and tell it to look up this column for the lookup value.

Peter Gaillard

The VLOOKUP function in MS Excel will interpolate between the looked up
values if it's used in the form:  =VLOOKUP(<Lookup_value>,
<Table_array>, <Col_index_num>).  Thus if Lookup_value falls between
two values in the table the function will find the closest match. However, if the Table_array can be constructed so that it contains all possible values of Lookup_value, then the problem should not arise. If this isn't possible then an alternative might be to 'round' the distances appropriately so they will always find an exact match in the table.
Martin Fenn-Smith, Cadiz, Spain.

The Excel LOOKUP function would certainly allow you to have your athletic track event times in ascending sequence with the corresponding points in descending sequence. However, if you particularly want both to be in ascending sequence then instead of using the actual time as the lookup-value, use its reciprocal instead i.e. 1 / time (or 100 / time or something similar). That way the slower times will be at the beginning of the table corresponding to the lower points.
Jim Burgess

I do not see why Anna should not use the Excel VLOOKUP function for awarding points in timed athletic events. If she sets up a look-up table with times in ascending order in the left-hand column and points in descending order in the right hand column, it should work without any problem. The only slight complication is the necessity to convert times into decimal format rather than hours, minutes and seconds.

David Beever

I believe the easiest way to solve this problem is to translate the result into one where more points are scored for larger results. This can be done by multiplying the values in the table by minus one. Therefore, as times get better, they become larger once multiplied by minus one. In the lookup function, the lookup value is then just the result multiplied by minus one, and references the table with the times (all negative) and the relative scores.
Jon Nicholas

PACK A PC

In early July my son and I are doing a sponsored bike ride around the circumference of Essex in aid of the Air Ambulance Fund and we are anticipating taking a laptop computer with us so that we can update details of our progress on a couple of web-sites. What would be the best, most reliable way of transporting the laptop? Take out the hard drive, carry the unit in our back pack, or securely suspended on a bike rack? Has anyone else gone down this road?

Peter L. Parker, via email

I have a Sony Vaio laptop and also an Apple Power Book. The Sony has been around the world about three times with me, and also and probably more importantly - in my backpack on a motorbike. Journeys have been several hours long. As long as the Laptop is 'shut down' and not on 'standby' or 'hibernate' then there is no problem how you are going to carry it.

I would suggest in a proper carry case though as they are padded to a certain degree and will protect against a certain amount of knocks and falls.There is no need to remove the hard drive at all.

Bob Sherriff

CAN YOU HELP?

I run a small business and I’m looking for a program that records client names and addresses, logs orders, reports when a customer last placed an order and flags up an entry if there is no repeat business within a certain time frame. Can anyone recommend suitable software?
Monty Portman, via email

I am a professional diver and frequently work on wrecks and submerged structures, recording data and coordinates etc., by hand, on a ‘slate’, which is cumbersome and has limited storage capacity. Has anyone developed such an underwater organiser or PC, preferably with a large keyboard or touch sensitive screen (for handwriting recognition) or an illuminated display?

S Lindley, via email

I have written a macro in Excel 2000 to generate acronyms from sets of letters. The macro generates all possible combinations of the letters and produces a table of results. I have been unsuccessful in getting the dictionary facility to operate from within the macro so I copy the table of results into Word and use its dictionary to identify incorrectly spelt words. Can anyone suggest a way to use the dictionary from within a macro, to identify incorrectly spelt words and print a list of correct ones?

Les Thorpe, via email