Technology Q&A. .
Q. I have an Excel spreadsheet that links to both current and deleted files, and therein lies the problem. Try as I might, I can't break the links to the deleted files. I've used the search command to find them, but to no avail. Any suggestions?
A. Indeed, that can be a problem. One of Excel's 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 you're 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 worksheet--not just the asterisks--because the asterisk is a wild card! It's a catch-22. You see why this is making me nuts? Please help!
A. Relax. There is a solution. For those who aren't aware of the power of wild cards, let me take a moment to bring you up to speed. A wild card is a keyboard character--in this case, an asterisk--that stands for any keyboard character(s); it's not unlike wild cards in poker. So, for example, if you're doing a search with Explorer's Search tool for the file C:\clients\zignazerwitz\taxproblem.doc, but you don't remember (or can't spell) the client's name--Zignazerwitz--you 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 asterisk's wild card attribute by placing a tilde ('~) before it like this: ~*
Now Find and Replace produces this screen, replacing all the asterisks with 4s:
Q. What happened to one of my favorite Windows utilities--ScanDisk? 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 that's just as good as ScanDisk. However--and this may be hard to believe--it has no name, and for reasons known only to Microsoft, it's hidden. It's 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 doesn't 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 it's OK you can download a file from the Web that will adjust the computer's clock every time you go online. I'll tell you more about that later.
If you have Windows 2000 or later, Windows can be set to automatically adjust the clock's 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 below).
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 computer's internal clock. To get connected, go to http://www.eecis.udel.edu/~ntp/software/index.html.
Q. I've 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, I'm looking for a way to get Word to emulate WordPerfect by showing the formatting of what I type--a very helpful feature. Any ideas?
A. Starting in Office 2000, you can do that. Click on Help on the toolbar and then on What's 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. Here's how to set up such a button: Go to Tools, Customize, and if it's 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).
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 it's getting very hard to port that file to my laptop from my desktop. I know I can archive it, but then it's 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 solutions--one won't 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, it's 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 format--as HTML or plain text; if you want, you later can convert either version of text to a Word file, but that's an extra step.
Now, what you probably didn't 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 Outlook's native database, you'll find it easy to index and search those more familiar text files.
If you're 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 (page 109), we described in this column how to use the dollar sign ($) to make an Excel formula constant--so when you copy a formula to a different location on a spreadsheet it won't 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 text--not a formula--cell. 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 you've highlighted. If you don't highlight a cell reference, Excel will add the dollar sign only to the cell reference closest to your cursor.
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.
For this latest suggestion, thanks to Dan Olsen, who works for Utah's 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 firstname.lastname@example.org.
Because of the volume of mail, we regret that we cannot individually answer submitted questions. However, if a reader's 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 it's 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. It's virtually impossible to test them in all editions and it's equally difficult to find out which editions are incompatible with a function. I apologize for the inconvenience.
|Printer friendly Cite/link Email Feedback|
|Publication:||Journal of Accountancy|
|Date:||Jan 1, 2003|
|Previous Article:||Intentionally defective irrevocable trusts; estate planning for insecure times.|
|Next Article:||AICPA educates women about PFS value. (Inside AICPA).|