Q. I work with many large spreadsheets and regularly print several different sections of them that are scattered throughout various workbooks. It's awkward and time-consuming to format individual areas each time. Is there an easier way to do this?
A. There are at least two ways. One requires preparing a macro, but frankly, it hardly seems worth the trouble because the alternative---using Custom Views--is easy to set up and both fast and effective.
Here's how to use Custom Views: Select the first print area, select the orientation, margins and other settings, and then go to View, Custom Views and click on Add (see screenshot below).
This triggers another dialog box called Add View (see screenshot). Pick a descriptive name for the view you want to define--such as 1st Quarter--being sure to also click in the Print Settings check box and click on OK.
You can continue to add more print views, all of which will be saved in your workbook for later use.
When you're ready to print, go to View, Custom Views, select the view you want, click on Show and then run the print command.
Q. Every now and then I get a message on my screen that says a document I'm trying to open is "in use by another user" or is "locked for editing by another user," and my only option is to open it as a read-only document. The bottom line is I can't work on the document even though I know no one else has it open. What's up?
A. There are several possibilities, and to understand what's going on, you need to know how Word 2000 and 2002 handle documents. When you open a document, Word creates a special temporary copy of the file, called an owner file (Word recognizes you as the owner). If you look in the folder where you stored your file, using Explorer, you'll see the owner file listed with a tilde (~) and a dollar sign ($) appended to the front of the name. So, if the file name is Help.doc, the temp file will be ~$Help.doc. When you close your file, the temp file automatically is deleted. But when it fails to delete, for any number of technical reasons, it will trip an "in use" or "locked" message when you try to open it later.
When Word fails to shut down properly (because of a power surge, a power interruption or a programming snag), the temp file doesn't get deleted. So when you try subsequently to open your file, Word, in a misguided effort to protect your file, either won't let you open it or lets you only view it as a read-only file.
There are two other possibilities: Someone else on your network has the file open or you accidentally opened Word twice (by clicking on the icon more than once) and the second copy of Word is being barred from displaying the file.
So now that you know the possible culprits, what do you do to get the file open? First, be sure that no one else is using the file. Once you've verified that, shut down all instances of Word on your system. While you can try just closing them in the normal way, sometimes that doesn't work because (and I know this sounds very strange) segments of the application get left behind in memory. So to be absolutely sure you've closed Word, activate the Task Manager by pressing Ctrl+Alt+Del, producing this screen:
If Word closed correctly, you'll see nothing that has the word Winword or Word in the list. In the above screenshot, you'll see that I have this Tech Q&A file open in Word--c-TQA-april-03.doc.
If you see a task with Word in the title, highlight it and click on End Task. Then close Task Manager by pressing the Esc button.
As a final step, go into Explorer, open the folder that contains the document you tried to open, and if you see an owner file as described above, delete it.
That should do it.
Q. My company's mailing list, which is in Excel, has thousands of names and addresses, and many of these contacts contain a nine-digit ZIP code. My manager wants to convert those with a nine-digit code to a five-digit code. Short of going through the list manually and cutting off those last four numbers, is there a fast and easy way to do that?
A. Yes, there is. Here are the steps:
* If the ZIP codes are in column D, create a new column just to the right, in column E.
* Enter this formula in E1: =Left(D3, 5) and then copy it into all the rest of the cells in column E.
* Highlight the entire column D and copy it (Ctrl+C) into the Clipboard.
* Go to the Edit menu and click on Paste Special and select the Values radio button and click on OK. Delete column D; by doing that, column E, with the now shortened codes, becomes column D.
Q. I'm preparing a report in Word. While I want to paste a portion of an Excel workbook into my document, I don't want those who view the document to be able to change the numbers or in any way edit what I paste in.
A. I know just want you mean, and many people may miss the subtle difference between incorporating an Excel workbook into Word and pasting just a portion of it into a document. If I copy a workbook or even a portion of it into a Word document, that portion is still a live Excel application that can dynamically calculate and be manipulated. On the other hand, if you take a snapshot of the Excel workbook and copy that into the document, it's like copying a static photo and pasting it into a document.
Here's how to do the latter: Select the workbook range you want to copy and then hold down the Shift key while you choose Edit, Copy Picture. When the Copy Picture dialog box opens, accept the defaults and click on OK (see screenshot).
Go to the Word document where you want to paste the image and insert it (press Ctrl+V). Now your colleagues will be able to look at, but not change, it.
Q. A friend told me Word has a quick and easy way to save little scraps of text for later use. I've searched around and cannot find any mention of it under Help. Can you assist me?
A. I certainly can. However, you should be aware that you can save not just little scraps but also big chunks of text, and the feature works just as well in Excel.
Suppose you're in a Word document (or an Excel workbook) and you come across some material you want to save; it could be as small as a word or as large as many pages. Highlight what you want to save and drag it to the desktop. If the desktop is not easily accessible, copy the material with Ctrl+C and then go to the desktop and paste it with Ctrl+V (see screenshot).
When you want to access the scrap, just click on it. You then can copy it to a document (or a workbook) by reversing the process.
Q. I know I can add a column of numbers by writing a formula such as =sum(A1+A12, or by clicking on AutoSum ([SIGMA]). But is there a way to do this using a faster keyboard approach?
A. Yes, and the keyboard approach is much, much quicker. Just click on a cell at the bottom of any column you want to add (it can even be many cells below the last number), hold down the Alt key, type in = and then press Enter.
Q. Is there a way to operate the mouse cursor if my mouse fails? I've had the experience twice when I was at a client's office with my laptop: In one case the mouse button broke, and in the other, the mouse software malfunctioned.
A. I do know of one program, called the Mouse Emulator, that let's you do that. When you run it, it shows you which keys move the mouse cursor. The program is free and runs under Windows NT/9x (that's 2000 and XP). To download it, go to www.geocities.com/pronto4u/mouseemulator.html.
Q. I get many e-mails that are continuing "conversations"--that is, I send an e-mail to a colleague, she sends me a response with a copy to three other people, who then respond with copies to everyone. After a while, the number of related e-mails can grow to a dozen or more and keeping track of them can be very hard. Any suggestions?
A. Outlook has an elegent solution to the problem. As long as the messages share the same Subject, gathering them together for easier viewing is a snap.
Open your Inbox and click on any of the messages in the thread. Then select View, Current View, By Conversation Topic. In the screenshot below, the three e-mails share the same Subject (Re: letter), and by clicking on the plus (+) sign on the left side of the screen, the entire thread now will be grouped under one header.
Q. I get loads of junk mail and it jams my inbox. Is there some way to stop it?
A. The solution to that problem may be as elusive as the design of an antigravity, perpetual-motion machine. Seriously, some of the best computer minds are struggling with the problem. Unfortunately, a larger number of equally top-notch computer minds are searching for ways not only to expand spam (that's what junk e-mail is called) but to thwart any technical efforts to block it. So you have a dedicated force butting heads with an equally dedicated obstacle.
Your Internet provider (such as AOL or Earthlink) has developed ways to filter some of it, but much still slips through. Since most spammers get addresses when people fill out forms on the Internet--such as when they buy something at a Web site or register for a Web service--one fairly effective way to stop spam is to create a second address that you use only for filling in forms. That way you can keep your private e-mail address just for friends and colleagues.
Another option: When asked to provide your e-mail address on a Web page or when posting to newsgroups, alter it in such a way as to trick spammers who troll for addresses but not confuse your friends and colleagues. For example, if your e-mail address is jones@internet. com, make it jonesNOJUNKMAILOinternet.com. Many users will know to remove NOJUNKMAIL from the address before using it, but trolling software programs aren't that smart.
Granted, neither solution is perfect, but both can cut down on the spam volume. Also, there's talk of a federal law to make spare illegal, but it's unlikely to pass the free speech provisions of the U.S. Constitution.
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.
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 email@example.com.
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.
* Remove a Toolbar Button: Hold down the Alt key as you click on the button you want removed and drag it off the toolbar.
* Internet Explorer: Alt+D puts your cursor into the address bar; there's no need to use the mouse. For many Web sites, you don't even have to type the full URL. For example, if you want to get to the New York Times site, just type nytimes (without a www. or a .com) and press Ctrl+Enter.
* Word: To change the capitalization of your text from/to lowercase, uppercase or initial caps (only first letter is capitalized), select the text that you wish to alter, and press Shift+F3 to toggle between the choices.
|Printer friendly Cite/link Email Feedback|
|Publication:||Journal of Accountancy|
|Date:||Apr 1, 2003|
|Previous Article:||Amnesty for offshore tax evasion: April 15 is the last chance to avoid certain penalties.|
|Next Article:||AICPA conferences 2003.|