My version works in any table but note that, like yours, although it works on all objects within a cell including graphics, it won't work if a graphic object is selected when you run the macro. This is a limitation of Word and is easily avoided. Neither will it work if the selection extends over more than one cell. My 'clear all cells but this one' macro works, copies the contents of the cell containing the selection point, wipes the entire table, then pastes the cell back into place. There are probably other ways to code it, but this must be about the simplest. It ought to work reliably, but if something does go wrong and you're left with an empty table, remember that you can manually paste the copied original from the clipboard.

Both macros are in the file TableMacros.dot and require Word 2000. They will fail with earlier versions. Copy the file to your hard disk and go to Tools/Templates and Add-Ins. Click Add and then browse to TableMacros.dot. Select it and click OK. Turn on the Tables and Borders toolbar. Right-click it and select Customise. On the Commands tab scroll down the Categories list to Macros. Drag TemplateProject.Table Macros.TableFill with ThisCell and .TableCIearAIIButThisCell from the command list to the toolbar. Rightclick each one in turn and edit the names down to something more manageable. Close the dialog.

Tempting though this may be, there have been many reports of lost or corrupted data when using master documents. If you want to split a document into chapters -possibly with chapter/page numbering so you don't have to reprint the entire document when one chapter is edited use section breaks instead, or split the document up into separate, independent files.

By default, Word 2000 turns anything it thinks is an email address or a URL into a live, working hyperlink. There is a snag to this: if you mis-type the address, and then correct it by using the cursor keys to 'get in' and edit it, the underlying hyperlink won't update. You need to right-click on the link, then Edit Hyperlink.

If you find this auto-hyperlink feature irritating -after all, it's not much use in a printed document -you can turn it off from Tools, Autocorrect, Autoformat As You Type. You will still be able to create hyperlinks by highlighting text or graphics, then pressing Ctrl & K. Although Word will fill in anything that looks as though it's a valid address as the link, you can override this manually and have, say, 'Visit my website' or 'Email me' as the visible title of the link.

This is one of the most useful, and most frustrating, features of Word. If you frequently mis-type a word, eg Intenet or Rebbot for Internet and Reboot, then right-click on the red-underlined word, but instead of clicking the relevant suggestion, go to the Autocorrect option on the context menu. You'll see the same suggestions, but your mistake will get added to the Autocorrect list. You can also remove entries (Tools, Autocorrect...) and make exceptions, so PCs, for instance, doesn't get changed to Pcs.

You probably know that you can select columns in a table by clicking at the top of them. But you can also do this for ordinary tabbed text? Alt & drag selects a rectangle of text anywhere on the page.

Trying to select more than a window-full of text can be frustrating, especially with a fast display card, as the highlight tends to shoot back and forth past the intended selection. Left-click once where you want to start, use the scroll bar to navigate until the end of the intended selection is visible, and Shift & click again.

Despite Word 2000's single document interface, there is a way to save all open documents, or close them, without closing Word. Hold down the shift key as you drop down the file menu and you'll see two new commands, Save All and Close All.

If you want to create your own custom tabs on the File, New dialog, it's easy. Just navigate to your main template folder in Windows Explorer, and create a new folder within it.

It's a good rule to use paragraph or character styles to format text, rather than applying formatting directly. This saves time in the long run and keeps documents consistent. However, rules were made to be broken, and there may be times when you've created some custom formatting that you'd like to repeat. If you click the Format Painter button (it looks like a brush) then 'dip' it in the formatted text you want to copy, you can then 'paint' other text by dragging the brush through it.

On a related note you can see a list of the paragraph, character and direct formatting of any character by clicking on the 'What's this' button (arrow & question mark) then clicking in the text. Click on the button again (or just resume typing) when you've finished.

If you're used to organising your presentations in PowerPoint's outliner, then remember that you can also use Word's rather more powerful equivalent. Each top-level entry denotes a new slide, with lower levels forming nested, bulleted lists. 'Normal' text is ignored. A tip-within-a-tip: don't bother trying to import the Word file into PowerPoint, instead, while still in Word, go to File, Send To, PowerPoint.

The free Photo Album add-in is available from http./ /officeupdate.microsoft.com/ 2000/downloadDetails/ album.htm. This lets you create a photo album from images on disk, a scanner or a digital camera. You have a choice of the number of pictures per page, frame style, backgrounds and captions: the last are generated from the image file name. The whole business takes just a few mouse clicks.

Having done this, you can use the Pack and Go Wizard to send the entire presentation or album to someone else (File, Pack and Go). If they don't have PowerPoint, you can include the PowerPoint standalone viewer. There is an option to span multiple floppy disks: this takes time as the files involved are large, but the process just involves inserting new disks when prompted.

Unpacking the viewer and presentation is not granny-proof, so it would be wise to include instructions. Two files need to be sent -the ppz file itself and pngsetup.exe. The recipient needs to run the latter, which will ask for a destination folder. This has to be typed in as there's no Browse button, and the folder name must be no longer than eight letters, without spaces: one for nostalgia fans. This will decompress the various files needed (again with prompts to change floppies, if applicable), and the recipient can then run ppview32.exe from the aforementioned destination folder. This will produce a standard File Open dialog with a ppt file visible in the contents. Select this, click Show, and the full-screen slideshow will begin.

When you use the drawing toolbar, you can drag the nested palettes off the AutoShapes menu to create standalone toolbars. This tip (and the next two) work in all Office situations where the drawing toolbar is visible.

If you double-click instead of single-clicking on a shape tool, it becomes 'sticky': you can continue to use the tool to create further shapes without having to select it each time. Click the tool again, (or any other tool) to unstick it.

Inherently stylish

Having chosen fill and line styles and colours for a drawing object, you can right-click and Set AutoShape Defaults and all new shapes will inherit these styles, except for text boxes. Note that this last exception doesn't work for Word and Excel.

In addition to the standard Office keyboard shortcuts, PowerPoint has a few that are all its own. Ctrl & M inserts a new slide, Ctrl & D duplicates the current slide, Ctrl & G toggles the guides on and off, and F5 starts a slide show.

If you're giving a slide show on screen, you can pause the show by pressing the full-stop key (or b). The screen will go black. Press the key again when you are ready to continue. If you'd prefer a white screen, press the comma (or w) instead.

Designing a good presentation takes special skills. As well as being a good communicator, you need a modicum of artistic talent if you plan to stray from the predefined templates. The much-maligned Office Assistant can be quite useful here, with the 'lightbulb' suggestions offering useful advice on layout and spotting potential problems. You can fine-tune the rules from Tools, Options, Spelling and Style.

What you don't do is have a field in which you manually enter the age. If you did that, you would have to come up with a way to update the age field every time the table was opened, or the clock struck 12 when it was already open. This is technically possible, but a very clumsy way to handle the problem. It would also be a waste of space because you're holding the same information twice -given that the computer knows today's date, the age can always be calculated from the birthdate.

I'm glad you didn't have more functions in the code, but it wouldn't matter because I already had a good idea about what the problem is, having seen it before. Your problem is you have one control that gets its result from an expression calculated within another control, and you can't do that for certain functions. Your situation is quite complex because of the way you've structured the data, so I'll use a simple example to explain what's wrong. Let's say you had a control on a form with a ControlSource and ControlName of 'Boxes'. Now lets say you place a text control on your form, with a name of 'Text1 ' and a ControlSource of ' = Sum([Boxes])'. So far all is well but, were you to add a second textbox to the form and set its ControlSource to '= Sum([Text1])', you'd see that your error instantly appears, and that's because you can't use a calculated function (in this case 'SumO') on a calculated expression.

Access has a habit of presenting information in grids that only show part of each field. Of course, you can fiddle with font sizes and column widths, but a handy tip is to press Shift-F2 to zoom into a field. What really happens is that the field contents open in a separate window, making it much easier for serious editing. This also works in the Query designer, for example to save you typing long expressions into a tiny Criteria field.

The official way to store images in Access databases is by using OLE (object linking and embedding) objects. Although this is easy to do, there are many disadvantages, including a bloated database size, unpredictable behaviour on different systems, and problems accessing the graphic through code. One better way is to keep the images as external files, storing just a path name in the database, and using the Image control to display the graphic on a form. If you need to store the image itself within the database, save it as a BLOB (Binary Large Object) rather than an OLE object. You can display it by writing code that saves the image to a temporary file before showing it on a. form. Example code is in Microsoft's KnowledgeBase or is widely available on the web. In this case, you can get better performance by keeping images in a separate Microsoft database (MDB) and linking the table.

Null values are in Theory a ‘good thing’, because they let you distinguish between unknown and empty values. In many cases, though, null values are a nuisance. To prevent null values in a field, open the table designer. For text and memo fields, set Allow Zero Length to True and the Default Value to "" (two double quotes). For other field types, ensure that there is a Default Value. Note that this will not affect existing records. If you have to work with null values, use the IsNull function or the special criteria IS NULL and IS NOT NULL in the query designer. In general, if you are getting unexpected query results, null values are often the cause. Fields with null values will never be selected by a query unless you specifically ask for them.

The screen shots show me that Access isn't being random in its allocation of Word table contents to Access record contents. In fact, what its doing is selecting values based on the carriage returns in your Word document. You'l1 need to modify your Word document so that you can export the table eventually into a tab-delimited text format ready for import into Access. This is quite a long process, with some repetitive steps, but it will achieve your desired results at the end of the day. You're going to kick off by temporarily replacing all the carriage returns paragraph marks) in the document with a character that isn't already used in your table, such as the tilde '~'. To do so, follow these steps:

Access is generally regarded as case-insensitive. However, you can do case-sensitive queries using the StrCornp() and InStr functions. Use StrComp to match a whole field, and InStr to match any part of the field. For example, here's how to search for 'van' but not 'Van' in an Artist field. Open up a query in design mode, add the required fields to the grid, and then enter the following at the top of a blank column:

InStr (1, [Artist], "van",0)

Access will prefix it with "Expr1:" but don't let that worry you. Now enter:

>0

in the Criteria row and run the query. This will return 'van Eyck' but not 'Van Gogh'.

In an Access database, if you name a macro AutoExec it will run automatically when the database is opened. If you press Shift, the macro will not run. If you are really insistent, you can prevent Shift from bypassing the start-up code by setting the AllowBypassKey property to False. Unfortunately you have to do this using Visual Basic code, but a full example can be found in online help. Search for AllowBypassKey.

SQL sub-queries are a powerful technique for data manipulation. For example, imagine you have two versions of the same table, and you want to append rows from one to the other, but only where they do not already exist. Assuming a primary key field called ID, here is the SQL: INSERT INTO artists

SELECT NewArtists.*

FROM NewArtists

WHERE NewArtists.id Not In (select Artists.id from Artists);

Stuck with a corrupt MDB? According to Microsoft, the undocumented decompile mode for Access 2000 can fix corruption that the ordinary repair utility cannot manage. To use it, run Access with the command-line argument: /decompile "dbname" where dbname is the full path to your database. You can do this by editing a shortcut. This tricks Access into thinking it must change the binary format. Note: before trying this or other repair attempts, make a backup of the errant MDB.

If you share an Access database over a network, consider using one MDB for all the forms, reports, macros and VBA code, and another for the data. Use the Link table feature to attach the database tables to the application MDB. Now you have the option to store the application MDB on workstations while keeping the data in a shared location, speeding performance. The downside is that if you update the application, you must take care to replace all the copies, perhaps using a login script.

Two ways to improve performance are to defragment your hard disk and to use the Compact and Repair utility in Access to optimise an MDB. When you do this, it is best to defragment first. If you Compact first, the MDB is likely to be surrounded by used disk space, slowing access when data is added. Defragment followed by Compact is likely to leave the MDB with adjacent free space, which is the ideal scenario.

Indexing a column in a table speeds searches on that column, but every index you add slows down database updates. The value of an index therefore depends on how the data is used. If you have lengthy update routines on a large database, you can often improve performance by removing indexes other than the primary key before the update, and rebuilding them later, using VBA code to automate the process.

There must be a way to reset the working area in a similar fashion to selecting the print area, but I can't find it. Deleting 65,000 rows is not a viable option.

If you use any cell beyond your last active cell, Excel automatically redefines the work area. The normal solution is to delete all the columns, then all the rows, between your preferred bottom-right cell and the stray one far away. Then Save, Close and reopen the file. Alternatively you can clear that alien cell and then use this simple macro:

Sub ResetRange()

ActiveSheet.UsedRange

End Sub

To quickly adjust the width of various chosen columns, hold down Ctrl as you click the column heading letters, then drag a heading boundary of any of the columns. A tip box will display the column width. To adjust the width of adjacent columns, hold down Shift as you click the first and last columns, then drag the right-hand boundary. These tips apply similarly to rows.

To create a formula, click the often-neglected Paste Function tool (marked fx) on the Standard toolbar. Select a function and a description appears in the Paste Function dialog box. The Help button leads to examples of use of the function. As an alternative to this tool, click the equals sign in the Formula Bar and the Name Box on the left will list the most recently used functions.

For quick calculations on a worksheet, use the AutoCalculate feature. Select a range of figures and a total of them will appear in the Status Bar. Right-click on this total and you can change it to an average or a count of the numbers in the range. AutoCalculate can also show you the biggest or smallest number in the range. If the Status Bar isn't displayed, select it on the View menu.

To line up drawn objects, like rectangles, with worksheet cells first click Draw, on the far left of the Drawing toolbar, then Snap, then To Grid. To line up drawn objects with each other click Draw, Snap, To Shape. To line up two different sized drawing objects with each other select them (by holding Shift as you click them both) then choose Draw, Align, Align Bottom.

The fastest way to create a chart is to select the full range of a table (including labels) and press F11. Excel will draw a column chart on a new interactive chart sheet, with the x and y axes labelled, colours chosen for the data series, and a legend box in place. You can right-click on any part of the chart to change any aspect of it.

You can often solve problems with links to other workbooks or Office applications under Workbook Options. Find this under Options on the Tools menu. Make sure the Update Remote References box is checked. If you check the Save External Link Values box you won't have to worry about maintaining links. However, if the link is to a large range it will greatly increase the size of your workbook file.

To create sub-totals in accounts, use the function =SUBTOTAL(9,B4:B9). The 9 is a function number and represents SUM. The range to sub-total in cell B10 is B4 to B9. You could repeat this for, say, B11 to B16 with another sub-total in B17. Then if you click on cell B 18 and the AutoSurn tool it will display the total of cells B10 and B17 only.

Getting decimal

To convert pounds and ounces to kilograms, use the CONVERT function found in the Analysis ToolPak. Enable it by using Add-Ins on the Tools menu. If the pounds are entered in cell A2 and the ounces in B2 then the formula would be =(CONVERT(A2,"(bm","g")+CONVERT (B2,"ozm","g"))/1000

Here Ibm and ozm, entered in quotes, mean convert from pounds and ounces to g for grams.

Confused about the difference between deleting a cell and clearing a cell? If you click a cell and then press Delete or the Backspace key, the cell's stored value is removed but its formatting and comments remain. If you clear a cell, everything goes and it's as blank as a cell on a new worksheet. To do this press Alt & e then a a (the Alt key with 'e', then 'a' twice).

If you have a multi-page worksheet to print, and you want the column titles or list field names to print on every page, click the worksheet and choose - File, Page Setup, and then the Sheet tab. Click the red arrow in the 'Rows to repeat at top:' box, then click then on row number 1 (or hold Shift and click on 1 and 2). Then click OK.

Double-click on My Computer and select the View - Folder Options menu item. When the dialog opens, click on the File Types tab and then scroll down the Registered file types list until you find the Microsoft Excel Worksheet entry.

Click on this to highlight it and click on the Edit button.

When the Edit File Type dialog opens, you should see the following entries:

Description of type: Microsoft Excel Worksheet

Content Type (MIME): application/vnd.ms-excel

Default extension for Content Type: .xls

Below that will be a box labelled Actions. There'll probably be four entries in there: New, Open, Print and Print to.

Open should be in bold, indicating its the default action. Select it and click Edit when the 'Editing action for type' dialog will open. In the 'Application used to perform action' dialog, you should see something like:

"E:\Program Files\MicrosoftOffice\Office\EXCEL.EXE" /e

Below that there'll be a checkbox labelled 'Use DDE'. Ensure it's enabled and that its entries read as follows:

DDE Message: [open("% 1 ")]

Application: Excel

Topic: system

The 'DDE Application Not Running' entry should be blank.

You should find that when you now click an Excel file outside of Excel it will launch Excel with your file loaded.

= IF(denominator = O,"",numerator 1 denominator) or divide-by-zero blank-cell calculation error where denominator is the cell that's doing the dividing, and numerator is the cell that's being divided, of course. Alternatively, you could use this formula in the affected cells:

= IF(ISERROR(numerator 1 denominator),"",numerator 1 denominator)

Those will work for individual cells, but you can go further and apply Conditional Formatting over a wider area: this example shows you how to prevent the appearance of the #DIV/0! Indicator in all the cells in a specified column. Follow these steps:

1 Select your column - we'll use B for this example, because I'm bored with always using A -then head to the Format menu, click on it and select the Conditional Formatting menu item.

2 When the dialog opens, click on the down arrow for the combo box under Condition 1 and select the Formula Is value.

3 Now enter the following in the edit area that appears when you select Formula Is;

=ISERROR(B1)

and then click on the Format button.

4 Head to the Color drop-down, click on it and select the White square.

5 Finish by clicking on OK twice to clear the dialog boxes.

Furthermore, if it could be proved that there is no significant bias in the Lottery draw, you could be sure that this set of numbers is no more likely to come up than any other set, no matter what the past performance. If there is no bias, your choice of numbers does not increase the chance of winning, yet it reduces the payback if the gods do smile on you. We'll come back to this later.

Off to one side of the list where there is some free space, enter two column headings, 'Ball' and 'Frequency'. In the Ball column build the list 1 - 49. The easy way is to enter '1' into the first cell (press [Return] then move back on to it). Go to Edit/Fill/Series. 'Series in' should be set to 'Columns' and 'Type' should be 'Linear' The step value should be 1 and the stop value 49. Click OK. In the cell to the right of '1', enter =COUNTIF( Follow this with the address of the range containing the results. Each cell reference must be made absolute by prefixing each row and column index with a $ symbol This is so that when you copy this formula down the list, Excel doesn't adjust the range address. Now type a comma, followed by the address of the cell containing the '1' (no dollars required). Close with a ')' and press [Return]. Here is an example - your addresses will probably be different. =COUNTIF ($B$2:$G$400, I2)

Assuming cell I2 contains '1', this will count all the '1' balls in the range B2 to G400. Select this cell and double-click the small black box on its bottom right-hand corner -the mouse pointer turns into a cross -to copy the formula down the column as far as '49' You now have a list of counts, but not in order. Highlight the range of ball numbers and frequencies and use Data/Sort to sort them into descending order of frequency. This brings the most popular numbers to the top. As new lottery results are entered you will have to extend the range in the formula, re-copy it, then re-sort. You can avoid changing the formula too frequently by extending the range way beyond the latest results, so the analysis takes in blank rows that have yet to be filled. Put a reminder in the row after the last one in the range to tell you to update the formula. One way to make the process less troublesome is to give a name to the range being analysed (Insert/Name/Define). This name can then be used in place of the range address =COUNTIF(WinningNumbers,I2)

When the data set grows beyond the bottom of the named range, all you have to do is edit the name's specification in order to extend its scope. This removes the need to change the Countif() formula. You could automate the housekeeping tasks and the weekly re-sorting by way of macros and buttons, but that's another story. You now have the answer to your question, but let's go a bit further and see if we can get a clue as to whether the Lottery draw is exhibiting bias. After nearly 500 weeks the most popular numbers have come up almost twice as often as the least popular. Surely this is an indication that the top numbers are more likely than others to come up in the future? It's tempting to think so, but look a bit deeper and you'll find good reason to doubt this notion. If the draw is genuinely random, each number has an equal probability of being selected. Yet as the results accumulate, there is still bound to be some variation in the frequencies with which various numbers come up. The question is, do the results so far come close to what you'd predict for an unbiased draw? This isn't the place to explore statistical analysis, so let's take an experimental approach.

The distribution of frequencies can be analysed with Excel's Frequency() function. First, we iron out minor variations by grouping the frequencies into ranges. An interval of five seems to be suitable. What we're counting now is the number of balls that came up between 46 and 50 times, the number that came up between 51 and 55 times, and so on In another column enter a list of intervals. I suggest values of l00, 95, 85 and so on down to 40. Select the blank cell to the right of l00 and then highlight the column of blank cells beneath it down as far as 40 Click in the formula bar and enter. =FREQUENCY(range1,range2)

Change 'range1' to the range address containing the frequencies generated by Countif(). Change 'range2' to the range containing the interval values. This is an array formula, so don't press [Return] to enter it. Press [Ctrl][Shift][Return]. The formula should appear within curly brackets and the results be placed in the highlighted cells. By itself, this distribution doesn't tell you much. But, run the same analysis on random numbers generated by Excel and you will get a similar distribution. Do it as many times as you like, and most of the time the distributions broadly correspond - the top-ranked numbers come up about twice as often as the ones at the bottom, with a characteristic bunching of results around the average frequency. This is an indication that the Lottery is probably behaving as it should do and that the top-ranked numbers are put there by the vagaries of chance. That being the case, if you were to analyse the results of the next 500 draws, a different set of numbers would be favoured to the same extent as the current leaders.