-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 15 September 27, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: SumProduct Multiplies Cells, Adds 'Em Up
2) Reader Challenge: Word Tables and Date Math
3) Reader Feedback: The Perfect Document
4) Review: You Perform Adds Nice Features To Outlook
5) Word: Keyboard Shortcut for Bullet Toolbar Button
Premium Edition Extra:
PowerPoint: Hide the Slide Show Navigator
-------------------------------------------------------------------
1) EXCEL: SUMPRODUCT MULTIPLIES CELLS, ADDS 'EM UP
-------------------------------------------------------------------
If you need to do some quick multiplication and total the results,
Sumproduct is an extremely handy function. With this simple
function you can multiply and add values in one fell swoop.
For example, suppose you have a three-column table in Excel. The
first column contains the name of the stock you own. The second
column contains the number of shares you own, while the third
column contains the current market price. The question is: how
much is your portfolio worth?
You could, of course, add a fourth column that contains a formula
to multiply the shares by the share price, then total this fourth
column. There's a simpler way.
For purposes of this discussion, let's assume you have the
following table. The values for cells A1 through C4 are shown
below.
Stock Shares Price
MSFT 100 27
PG 50 55
SYMC 50 52
In cell D5, enter this formula:
=SUMPRODUCT(B2:B4,C2:C4)
Cell D5 now displays the value of your portfolio.
SHORTCUT: CELL RANGE NAMES
Alternatively, you can assign the cell range B2 through B4 the name
Shares, and assign cell range C2 through C4 the name Price.
There are two ways to do that. Here's the slow way:
1. Select B2 through B4.
2. Use the Insert/Name/Define command.
3. Enter Shares in the "Names in workbook" field, then click on
Add.
4. Click Close.
5. Select C2 through C4.
6. Use the Insert/Name/Define command.
7. Enter Price in the "Names in workbook" field, then click on Add.
8. Click Close.
Note: At Step 4, you could modify the "Refers to" field with the
values of C2 through C4 and add the Prices name.
Now here's the much more efficient way.
Excel's help file tells you to select the range you want to name,
including the row or column labels. Since we don't care about row
labels in our example, we only select B1 through C4 -- which picks
up both column headings and all share and price data.
Use the Insert/Name command, then choose Create. When the Create
Names box appears, select Top row. Click on OK and both range
names are defined.
With the range names established, the portfolio-value formula can
be shortened to:
=SUMPRODUCT(Shares,Price)
-- James E. Powell
-------------------------------------------------------------------
EASY-TO-USE WEB HOSTING FROM $9.95
-------------------------------------------------------------------
All plans include 24/7 technical support, unlimited e-mail, state
of the art web site builder, control panel admin, FrontPage and ASP
support. Domain names just $12.95. No contracts required, and
we'll set you up for FREE in under 20 minutes. Sign up today at:
http://www.officeletter.com/thehostgroup.html
-------------------------------------------------------------------
2) READER CHALLENGE: WORD TABLES AND DATE MATH
-------------------------------------------------------------------
At press time there were no takers on last week's reader challenge
to put formatted, sequential dates into a Word table.
There might be a good reason, as TOL reader Doug Klippert pointed
out:
Trying to add Excel functionality to Word is an exercise in
Sadomasochism. Here is, I believe, the ultimate guide to Date
field calculation in Word:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
Here's the original problem:
http://www.officeletter.com/previous.html#datemath
If you have a solution, please let us know: [EMAIL PROTECTED]
-------------------------------------------------------------------
SPONSOR: WORD ADD-ON FOR CD AND DVD JEWEL CASE INSERTS AND LABELS
-------------------------------------------------------------------
Create your own beautiful and professional CD and DVD jewel cases
and CD labels --even DVD or VCD labels -- in just minutes! Perfect
for business, home, or hobby. Works with any type of paper or CD
label including centered label sheets. It's easy to make both front
and rear CD jewel case covers with side spines as well as CD labels
-- all in one package!
http://www.amfsoftware.com/affilcd.pl?affil+10057
-------------------------------------------------------------------
3) READER FEEDBACK: THE PERFECT DOCUMENT
-------------------------------------------------------------------
After reading our story last week about using different page
headers and footers on the first page of a Word document, Stan
Schwartz wrote us:
You recently had a tip about making different first page
headers. Maybe you could address this question in a subsequent
issue.
How do you create a consistently different second page header? I
have a business template with my logo and company information in
the header on the first page. I
want the second page, and all subsequent pages to have the page
number and addressee's name.
It seems I always wind up messing up the first page header when
I make changes.
Stan's not alone. I've accidentally destroyed my share of
documents. There are two approaches to solve the problem.
The first is to create the "ideal" document -- no text in the
document but with headers and footers the way you want them. Save
the file as a template. When you want to create a new document,
you simply base it on that template and you've got your headers and
footers the way you want them. Then just fill in the proper
header/footer text, and you should be all set. (Use the File/New
command, then pick the template you want to use as the basis of
your document.)
The other approach is to create the same ideal document, but simply
save it. When it's time to create a new document, open your ideal
document and use the File/Save As command. Of course, this means
it's possible to forget and simply use the File/Save command and
overwrite the ideal document. One method of avoiding this is to
use a file management tool (such as Window's own Explorer) and make
the ideal document "read only."
-- James E. Powell
-------------------------------------------------------------------
4) REVIEW: YOU PERFORM ADDS NICE FEATURES TO OUTLOOK
-------------------------------------------------------------------
Having been fortunate enough to publish a best-selling book on
Outlook, I receive a lot of requests from software publishers to
review software for Outlook. Some of it is good; some of it isn't.
One that I recently tested that falls into the former category
(good) is You Perform, an Outlook add-on from You Software
(http://www.yousoftware.com). The product is based on a collection
of utilities the company acquired when it bought Sperry Software
this summer.
You Perform adds a lot of what I would call non-critical but very
useful features to Outlook. Some of these features were present to
a degree in previous versions but have been dropped. Others build
on existing features in Outlook to improve usability. The rest add
new features to Outlook to simplify everything from adding e-mail
addresses to your Contacts folder to forwarding reminders to you
when you are away from the office. There are also a few features to
give non-Exchange-Server users the benefit of some Exchange
features without adding an Exchange Server.
The Add Appointments by Email feature lets others schedule
appointments with you simply by sending you an e-mail. You specify
the trigger value for the e-mail subject, the folder to scan for
incoming messages, and related options. When a message comes in
that fits the bill, You Perform schedules the appointment for you
in your Calendar.
The Add Email Address feature is one that long-time users of
Outlook might remember. Outlook no longer offers the capability to
automatically add sender addresses to your Contact folder, but You
Perform adds back this feature. It will check for existing entries
first in the folders you specify, then add the address for you
automatically if it doesn't find a match. You can also direct You
Perform to scan folders containing existing messages and create
contacts from them. The Auto CC/BCC feature automatically adds a CC
or BCC for outgoing messages that match the filter criteria you set
(such as those sent to a specific recipient or that contain
specific subject text).
The Attachment Forget-Me-Not feature is one we could all use now
and then. It scans outgoing messages for certain customizable
keywords like "attached" and "see enclosed" and warns you if you
haven't added any attachments before sending the message.
Auto Print scans the folders you specify and prints any new items
that show up in the folders. For example, you might use Auto Print
to print faxes that are sent to your Inbox by a third-party fax
application. You Perform's Hide Fax Numbers feature hides fax
address entries in the address book, making it easier for you to
differentiate between fax and e-mail addresses. Outlook 2003
identifies fax entries in the address book by itself, but this
feature will be handy if you use an earlier version of Outlook.
Although I've never worried much about duplicate items, I know that
many Outlook users like to periodically scan their data stores to
remove them. You Perform's Eliminate Duplicates feature can scan
for duplicate e-mails, contacts, and appointments, and either
remove the duplicates or move them to a folder of your choosing.
The Forward Reminders feature is handy when you will be out of the
office but still need to be prodded with your reminders. You
Perform will forward reminders to an e-mail address that you
specify, and can optionally forward notification for all popup
alarms. For example, you might have reminders forwarded to your
cell phone or alphanumeric pager.
Another handy feature I like is Schedule Recurring Email, which
lets you configure You Perform to send outgoing messages, with or
without attachments, on a scheduled basis. You specify the date
range, frequency, and start/stop date, along with a handful of
other options to determine when messages are sent. You can choose
one or more files as attachments or direct You Perform to attach
all files in a specified folder.
You Perform offers several other features for printing, working
with boilerplate text and dates/times, saving attachments,
converting vCards, and a Reply To All Monitor feature that
optionally warns you when you click Reply All and automatically
removes your address from the recipient list.
At $49.95, You Perform is a reasonably priced tool that is
certainly worth a look. You can download a full-featured demo
version from http://www.yousoftware.com.
-- Jim Boyce
-------------------------------------------------------------------
PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are learning about navigation
tricks in PowerPoint.
Why not subscribe to the Premium Edition today for just $12/year?
HTML format, extra content, access to all back issues, a fast
search engine, a printer-friendly format option, and no
advertising! Visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
5) WORD: KEYBOARD SHORTCUT FOR BULLET TOOLBAR BUTTON
-------------------------------------------------------------------
In the past we've discussed how to begin bullet lists from the
keyboard by entering one of a set of pre-defined keystrokes. By
way of review, remember that you can enter two dashes, a space, and
then your first bullet list item, and when you press Enter, Word
applies the default bullet style to your item, and positions you at
the next bullet list item. For other shortcuts, Premium Edition
subscribers should visit
http://www.officeletter.com/prem/v01/n33.html#bullets
Of course, this assumes that the feature is turned on -- it's part
of AutoCorrect's AutoFormat functionality. To be sure the feature
is active, use the Tools/AutoCorrect command, choose the
"AutoFormat As You Type" tab, and ensure that the "Automatic
bulleted list" option is checked.
If a bullet list item consists of more than one paragraph of text
(that is, you want to enter multiple paragraphs for the bullet but
keep the text for these subsequent paragraphs indented under the
same bullet), press Shift + Enter instead of Enter at the end of a
bullet list item.
To stop the automatic list and return to your "Normal" style, press
Enter twice.
If you have an existing list of items that you want to turn into a
bullet list, you can select the list and then click on the Bullet
List button on the toolbar. That works reliably.
Alternatively, you can press Ctrl + Shift + L. However, I've found
that this shortcut sometimes (but not always) applies rounded
bullets to the list but doesn't indent the text. The trouble is,
the behavior is inconsistent. (Note: There's a similar shortcut to
remove the bullets. Select the list and enter Ctrl + Shift + N.)
By default, Word assigns Ctrl + Shift + L to a function called
ApplyListBullet. The functionality applied by the Bullet List
button on the toolbar is slightly different: it actually performs
the function named FormatBulletDefault.
I'm a fan of using keyboard shortcuts instead of toolbar buttons.
To add a keyboard shortcut that works the same way, then, as the
Bullet List toolbar button:
1. Use the Tools/Customize menu command.
2. In the Customize dialog box, click on the Keyboard button (it's
at the bottom).
3. In the Keyboard dialog box, choose Format from the Categories
list. Pick FormatBulletDefault from the list of commands.
4. Click in the Press New Shortcut Key box and press the keyboard
combination you want to assign to this command. Since Ctrl + Shift
+ L is already assigned to ApplyListBullet, and it's easiest for me
to remember a similar command, I decided to use Alt + Ctrl + Shift
+ L, which Word hasn't already assigned by default.
5. Click the Assign button, then click on Close twice.
Now you can begin a brand new bullet list, or convert a list of
items into a bullet list, by pressing your newly assigned keyboard
shortcut.
-- James E. Powell
-------------------------------------------------------------------
CONTACT POINTS
-------------------------------------------------------------------
SUBSCRIPTION CHANGES
Your subscription record shows the following:
Email address: [EMAIL PROTECTED]
First name: subscriber at [EMAIL PROTECTED]
Last name:
Full name:
If you would like to edit the above data, click here:
http://www.office-letter.com/cgi-bin/mmp/[EMAIL PROTECTED]
To unsubscribe, visit:
http://www.officeletter.com/sub/substdremove.html
and enter your e-mail address.
SHARE YOUR TIPS: Send them to [EMAIL PROTECTED] and include
your permission to acknowledge you by name.
FEEDBACK: Send your suggestions and comments about The Office
Letter to [EMAIL PROTECTED]
RECOMMEND THE OFFICE LETTER TO YOUR FRIENDS AND COLLEAGUES
Point them to http://www.officeletter.com/current.html
MORE FREE TECH E-NEWSLETTERS: http://www.techletters.com
----------- THE OFFICE LETTER ----- www.officeletter.com ----------
Tips and Tricks for Microsoft Office - Published Weekly
Copyright 2004 Masterware, Inc. All rights reserved
Now In Our Fourth Year - ISSN: 1543-5768
Editor in Chief: James E. Powell
Contributing Editors: Jim Boyce (www.boyce.us)
Dick Archer (www.diseno.com)
