Technology Q&A
Unlink
stubborn sticky links in Excel
Remove the wild
card feature from the asterisk
Find the hidden
replacement for ScanDiskand other fix-it
features
Set the correct time on your computer
automatically...Get Word to provide format information
like WordPerfect does
Easy way to save multiple
e-mails in Outlook
An (even) better way to make
Excel formulas constant.
Key
to Instructions
To help readers
follow the instructions in this article, we use
two different typefaces.Boldface
type identifies the names of icons,
agendas, URLs and application commands.
Sans serif type indicates instructions and commands that
users should type and file names.
|
| |
EXCEL
Q. I have an Excel spreadsheet that links
to both current and deleted files, and therein
lies the problem. Try as I might, I cant
break the links to the deleted files. Ive
used the search command to find them, but to no
avail. Any suggestions? |
A. Indeed, that can be a problem. One of
Excels strengths is its ability to get
information by linking to other data sources,
including other workbooks. However, sometimes
those connections can cause problems, especially
when they are hidden or deleted. To its credit,
Microsoft recognized the problem and created a Delete
Links Add-In wizard to locate and delete
links. You can download the wizard from the
Microsoft Web site at http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q188449&.To install the
wizard after downloading, launch Excel and open Tools
and Add-Ins. Then click on the Browse
button and locate the file you downloaded, called
dellinks.xla and click on OK.
To run the wizard, go to Tools,
Wizard, Delete Links and follow the
on-screen steps.
|
| Q. I have a problem that is driving me
crazy! I know the asterisk (*) is used as a
wild card when youre searching
for a file or data. But what I need to replace is
a bunch of asterisks in an Excel worksheet and
replace them with some data. So I evoke Find
and Replace (Ctrl+H) and instruct it to
replace all the asterisks with my data. So far so
good. But when I hit Replace All,
it replaces everything in the
worksheetnot just the
asterisksbecause the asterisk is a wild
card! Its a catch-22. You see why this is
making me nuts? Please help! |
| A. Relax. There is a solution. For those
who arent aware of the power of wild cards,
let me take a moment to bring you up to speed. A
wild card is a keyboard characterin this
case, an asteriskthat stands for any
keyboard character(s); its not unlike wild
cards in poker. So, for example, if youre
doing a search with Explorers Search tool
for the file C:\clients\zignazerwitz
\taxproblem.doc, but you
dont remember (or cant spell) the
clients nameZignazerwitzyou can
use the asterisk wild card: *\tax problem. Now, back to the question. The asterisk
also is a wild card when you conduct a search or
a replacement, and it works in all applications,
including Excel. Thus, if you want to find all
the 4s in a worksheet, even when some of the
cells contain multiple numbers such as 34 and 44,
you evoke Find and Replace and
enter *4 in Find what:.

Excel does the search, finds
all the 4s, even when they are paired with other
numbers, and tells you their cell location.

But if your worksheet contains
asterisks and you want to replace them with 4s,
you need to tell Excel to cancel the
asterisks wild card attribute by placing a
tilde (~) before it like this: ~*.

Now Find and Replace
produces this screen, replacing all the asterisks
with 4s:

|
OPERATING
SYSTEM
Q.What happened to one of my favorite
Windows utilitiesScanDisk? Before I
upgraded to Windows 2000 I used ScanDisk all the
time to check and repair any problems it found on
my hard disk. But it seems Microsoft left it out
of Win 2000.
|
A. Yes, Microsoft replaced it with a new
utility thats just as good as ScanDisk.
Howeverand this may be hard to
believeit has no name, and for reasons
known only to Microsoft, its hidden.
Its not even listed in the Help
index.
There are two ways to launch it: Either
click on the My Computer icon on
the desktop and right-click on C:\ (or
whatever your hard drive is labeled) or go to Explorer
and right-click on C:\. Then
click on Properties, which
brings up a Local Disk (C:) Properties
screen and click on the Tools
tab (see screenshot below).
You now have three
options: You can click on Check Now,
which will perform the tasks formerly performed
by ScanDisk; you can click on Backup Now,
which, as the name implies, backs up files; or
you can click on Defragment Now,
which will defrag the files on the disk and, as a
result, speed up your computer.
Microsoft also hid
the functions that replace ScanDisk in XP, but
you can access them the same way; however,
Microsoft omitted the Backup Now option
for some reason.
While I have your attention about
checking the health of your hard disk, Windows
2000 and XP also contain Disk Cleanup,
a function that tells you which files, such as
accumulated temporary files, you can safely
erase. While Microsoft doesnt quite hide
the function, it makes it difficult to find.
There are two ways
to access it. Either right-click on C:\ and
this time go to the General tab
to see the Disk Cleanup button
just below middle of the screen, or left-click on
Start (the button that starts
the shutdown process for Windows; pretty
intuitive, eh?) and go to All Programs,
Accessories, System Tools and, presto, Disk
Cleanup.
|
| Q. The clock in my computer is always
losing time. Is there a way to fix it? |
A. First, check the internal battery. If
its OK you can download a file from the Web
that will adjust the computers clock every
time you go online. Ill tell you more about
that later. If
you have Windows 2000 or later, Windows can be
set to automatically adjust the clocks time
for you. Every time you connect to the Internet,
it will search out the exact time and reset your
clock. To program Windows to do that, just
right-click on the time in the systems tray and
go to Date and Time Properties
and click on the Internet Time
tab, placing a check in Automatically
synchronize with an Internet time server
(see screenshot at right).
If you have an older version of
Windows, you can still get your clock to run on
time. There are a bunch of free applications that
link to the Bureau of Standards atomic clock in
Boulder, Colorado, and, with a click or two, will
adjust your computers internal clock. To
get connected, go to http://www.eecis.udel.edu/~ntp/software/index.html.
|
WORD
Q. Ive been a diehard WordPerfect
user for a long time, but now I realize, although
reluctantly, I must join my colleagues using
Word. However, to make the transition less
painful, Im looking for a way to get Word
to emulate WordPerfect by showing the formatting
of what I typea very helpful feature. Any
ideas? |
A. Starting in Office 2000, you can do
that. Click on Help on the
toolbar and then on Whats This?
A faster way is to press Shift+F1. Then, click on the paragraph in which
you want the formatting revealed, and it produces
the following:'

You also can set up a toolbar
button that toggles the screen of selected text.
Heres how to set up such a button: Go to Tools,
Customize, and if its not already
highlighted, click on the Commands
tab. Under Categories, cursor
down to View and drag the Reveal
Formatting icon up to the toolbar (see
screenshot below).

|
OUTLOOK
Q. As much as I like Outlook, it does have
some shortcomings. For me the most serious is the
way it stores received e-mails: They all are
saved in one file along with my calendar and
contacts and sent e-mails. As a result, the file
grows to elephantine proportions, and its
getting very hard to port that file to my laptop
from my desktop. I know I can archive it, but
then its very difficult to actually see any
individual e-mails. Any suggestions? |
| A. Yes, that gigantic file is a pst
file, and it can quickly grow to 100 megabytes
and more especially when you store weeks and
months of old e-mails there. I have two
solutionsone wont cost you anything
and the other requires the purchase of a software
application. The general idea is not to store old
e-mails in the pst file, but copy them out and store them
in any other folder. You can create a folder
(subdirectory) called e-mails, and
then store them by month or by subject. Now, for
some neat ways to get the e-mails into the new
folder from Outlook. First
the gratis option: As you probably know,
its easy to save an Outlook e-mail message
as text. Just open the message and click on File
and Save As. You then have the
option to save it with a file name and a
formatas HTML or plain text; if you want,
you later can convert either version of text to a
Word file, but thats an extra step.
Now, what you probably
didnt know is that you can save a bunch of
e-mails as a single file. So if you have a group
of e-mails relating to one subject, you can store
them in a single message. To do that, select the
e-mails you want to gather together and, while
holding down the Ctrl key, highlight them one at
a time and then click on File, Save As.
This method provides a bonus.
Compared with trying to search through
Outlooks native database, youll find
it easy to index and search those more familiar
text files.
If youre willing to pay
for a utility, called ArchivER, that does the
above and much, much more, go to http://www.smtechnologies.com/prod13.htm and download a free evaluation copy.
The program can migrate Outlook data into
individual files and directories using a common
file format rather than the Outlook archive
folder.
|
AN (EVEN) BETTER WAY
In October
of 2001, we described
in this column how to use the dollar sign ($) to
make an Excel formula constantso when you
copy a formula to a different location on a
spreadsheet it wont automatically change to
reflect the move. Subsequently, a reader
suggested a better way: Double-click on the cell
to be moved or duplicated and type an apostrophe
() in front of the formula, transforming
the cell to a textnot a formulacell.
Then, after you copy or move it, you simply
delete the apostrophe and the cell reference will
remain unchanged. Another
reader recommended using the F4 function key to
toggle between making a cell reference relative
and absolute. Either double-click on the cell or
press F2 to edit the cell; then hit F4. It works
even when you highlight multiple cells. F4 adds
the dollar sign to the cell references
youve highlighted. If you dont
highlight a cell reference, Excel will add the
dollar sign only to the cell reference closest to
your cursor.
For this latest suggestion,
thanks to Dan Olsen, who works for Utahs
Division of Finance in Salt Lake City.
|
| |
| Do you have technology questions for
this column? Or, after reading an answer, do you
have a better solution? Send them to Senior
Editor Stanley Zarowin via e-mail at zarowin@mindspring.com. Because of the
volume of mail, we regret that we cannot
individually answer submitted questions. However,
if a readers question has broad interest,
we will answer it in a forthcoming Technology
Q&A column.
On occasion you may find that
you cannot implement a function I describe in
this column. More often than not its
because not all functions work in every operating
system or application. I try to test everything
in the 2000 and XP editions of Windows and
Office. Its virtually impossible to test
them in all editions and its equally
difficult to find out which editions are
incompatible with a function. I apologize for the
inconvenience.
|
|