Notes from the Help Desk…

Office 2007 now lets you save any document, presentation or spreadsheet as a PDF (without having to purchase Adobe Acrobat). However, you have to download a plug-in to add this functionality.

You can download the free plugin from Microsoft at:

http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

After you have downloaded the plug-in, to save a file as a PDF, click the Office button and select Save As | PDF or XPS.

If you are working with a formula that may refer to a blank cell or otherwise generate an error message, you can use a combination of the IF-THEN-ELSE and the ISERROR function to handle the #DIV/0! or other error messages that may appear.

The IF-THEN-ELSE has 3 parts: the condition, the TRUE condition, the FALSE condition. The following formula tests A1 for the number 100. If it is found, the cell displays the contents of A1. If it is not found, it multiplies another cell (B1) by 2:

=IF(A1=100,A1,B1*2)

By using one of the ISERROR functions, you can test for an error condition and use the IF statement to display a message or nothing at all. The common IS functions are ISERR, which returns TRUE for any error value except #N/A and ISERROR, which returns TRUE for any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

Suppose a cell is going to contain the average of several cells. If the cells are empty, the result will be division by zero and the #DIV/0! error will appear. The following formula will take care of this condition:

=IF(ISERR(AVERAGE(A1:A10)),”No Data”,AVERAGE(A1:A10))

If the formula AVERAGE(A1:A10) returns an error (most likely because there are no numbers in the range), the cell will display the words “No Data”. If there is no error, the cell will display the results of AVERAGE(A1:A10).

A snippet is a chunk of code that you can store in Dreamweaver to reuse later. Snippets can include HTML, JavaScript, ASP, JSP, etc. For example, let’s say you need to add several graphics to your Web site, and each graphic is in a two-cell table with the graphic in the upper cell and the caption in the lower cell. Since you will use the same table (with different content) several places on the site, there is no need to create the table each time. Simply create it once, save it as a snippet, and reuse it whenever you need to.

Dreamweaver also contains some predefined snippets that you can use as a starting point. For example, one snippet of JavaScript lets you add a Close button to any Pop-up windows you include on your Web site. This snippet obviates the need to create the code for each window.

Dreamweaver also includes snippets of code that lets you add a Browse for File… button, a variety of formatted tables, drop-down menus, a Print button, navigation aids such as breadcrumb links, and many more options.

PowerPoint 2003 comes with several design templates that you can use to add an artistic design to your slides. If you want a wider variety of designs from which to choose, consider downloading additional design templates from Microsoft’s web site.

If you want a more unique design for your presentation, or if you need to include your company colors and logo on each slide, consider creating your own design template. (This is more of a mini-lesson than a quick tip, but it’s well worth the time and effort.)

Using the Slide Master

To create your own design template, you need to know how to use the PowerPoint slide master. If you’ve never used this feature before, you can access it by selecting View | Master | Slide Master. The slide master is the template on which your presentation is based. It contains background graphics, formatting for titles, bulleted lists, header/footer contents, etc.

You can use the Slide Master to make global changes in your presentation. For example, suppose the design template you selected formats all slide titles in white text, and you want all titles to be blue. You can make this change by opening the slide master and selecting blue as the font color for the title placeholder. When you close the slide master and return to your presentation, all titles will be blue.

Who Cares about the Slide Master?

You do, if you plan to create your own design template. That’s where you tell PowerPoint which background graphic to use, as well as how to format titles, bulleted lists, graphical objects, etc.

Let’s Create a Design Template

Let’s say you need to create a design template for your company. It must include the company logo, the company’s colors as a gradient background on all slides, and it must appear in the Slide Design Task Pane so that you can apply it to any new presentation you might create.

Here’s how:

1. Create a new blank presentation.
2. Select View | Master | Slide Master to view the slide master. (PowerPoint will also open the Slide Master View toolbar, which you can use to close Slide Master View.)



3. Change the background by selecting Format | Background. Use this to select a background color, a blend of two colors (gradient), a texture, or a picture.
4. Add the company logo by selecting Insert | Picture | From file…
5. Drag the logo to place it on the slide and size it appropriately.
6. Select View | Header and Footer to add the date and/or slide number to each slide.
7. Close Master Slide View.
8. Save the presentation as a design template (*.pot).



9. PowerPoint will automatically save the template in C:\Documents and Settings\username\Application Data\Microsoft\Templates. Do not save the template in a different location; it needs to be in this folder in order for it to show up in the Slide Design Task Pane.
10. Close the presentation.

Using Your New Design Template

1. Create a new presentation, or open an existing one.
2. Select Format | Slide Design to open the Slide Design Task Pane.
3. Scroll through the design templates to find the one you created. (If you don’t see it, close PowerPoint and re-open it.)
4. Apply your design template to the active presentation by clicking on it.
5. You can also use the drop-down menu to apply the design to one slide, all slides, or to use it as the default design for all new presentations.

Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.

Extending a Cell’s Contents

The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:

• Text will be copied
• A single number will be repeated.
• A range of numbers will create a series based on the differences between the numbers
• A formula will be copied and all cell references will change appropriately.
• A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
• A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)

Smart-Tag Options

With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:

• Text or formula will let you extend with or without formatting, or just the formatting.
• Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
• Dates will let you fill days, weekdays, months, years, with or without formatting.

Right-Dragging the Extend Box

If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.

Double-Clicking Magic

Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.

There are a number of ways to calculate the time between two dates in Access. You can use a calculated field in a query, a calculated control in a form or report, or you can use a VBA procedure to perform the calculation.

If you need to calculate the number of days between two dates, the syntax is:

=[One date field] - [Another date field]

You can use functions to calculate the number of years between two dates:

=Year([One date field]) - Year([Another date field])

You can also use the DateDiff() function, which uses an argument to determine how the time interval is measured.

For example,

=DateDiff("q", Date1, Date2)

returns the number of quarters between the two fields. (In the example above, Date1 would be subtracted from Date2.) The other intervals that can be used in this expression are as follows: “yyyy” – Years, “m” – Months, “y” – Day of Year, “d” – Day, “w” – Weekdays, “ww” – Weeks, “h” – Hours, “n” – Minutes, “s” – Seconds.

Naming Date Fields

When naming date fields in Access, avoid naming a field Date, since this is a built-in Access function. Instead, consider using more descriptive names, such as BeginDate, HireDate, etc.

Hard Coding a Date

When calculating with dates in Access, you can hard-code an arbitrary date by using the following syntax:

#mm/dd/yyyy#

For example, you would use the following code to subtract a date from Jan. 1, 2006:

=#1/1/2006# - [Date Field]

Here are some useful shortcuts for Microsoft Access:

• Undo typing: CTRL+Z or ALT+BACKSPACE
• Undo changes: Press ESC once for current field, ESC twice for current record
• Insert the current date: CTRL+SEMICOLON (;)
• Insert the current time: CTRL+COLON (:)
• Insert the default value for a field: CTRL+ALT+SPACEBAR
• Insert the value from the same field in the previous record: CTRL+APOSTROPHE (‘)
• Add a new record: CTRL+PLUS SIGN (+)
• Delete the current record: CTRL+MINUS SIGN (-)
• Recalculate fields: F9

If you find yourself repeating the same searches in Outlook, you can create a custom search, save it, and execute it again by simply re-opening the saved search.

Here’s how:

1. Open the Advanced Find dialog box (Control+Shift+F, or click the Find button, then selection Advanced Find… from the Options menu.)
2. Enter your search options (for example, all mail with the word “password” in the message body).
3. When the search is finished, select File | Save Search…
   Name the search something you’ll recognize later, such as “messageswithpassword.”
   (You might want to create a folder in My Documents called “Outlook Searches.”
4. Click OK, and close the Advanced Find dialog
5. Open Advanced Find again.
6. Select File | Open Search…
7. Select your saved search, and click OK. Outlook will repeat the search.

If you find that it takes too long to switch from one email message to another, try turning off the Outlook “feature” that displays a Name Smart Tag in the message header and checks the user’s Instant (Windows) Messenger status.

To do so:

1. Select Tools | Options.
2. Click the Other tab.
3. Remove the check from Enable the Person Names Smart Tag. (The Display Messenger Status in the From fieldoption will automatically be disabled if the Names Smart Tag is not available.)

This is a situation that often comes up in our Excel classes:

“I need to add data to my spreadsheet, but I want to lock the cells that contain formulas so they don’t get overwritten.”

It’s easy to Protect an entire spreadsheet from the Tools | Protection | Protect Sheet… menu item. However, this option protects all the cells in the spreadsheet.

Unless…you first unlock the cells you want to remain editable. Here’s how:

1. Select the cells you want to be editable.
2. Select Format | Cells, and click the Protection tab:



3. Clear the check from the Locked checkbox.
4. Click OK.
5. Select Tools | Protection | Protect Sheet…



6. Leave all the default items checked. (If you wish, you can add checks to any features you want users to access.) Add a password if you want to prevent a user from Unprotecting the sheet from the Tools menu.
7. Click OK.
8. Users will now be able to edit only the cells that are unlocked. If a user tries to edit a locked cell on a protected worksheet, he/she will see the following message: