Technology Q&A: combine data from two cells into one ... Move Internet Explorer's temporary files to a different drive ... Let outlook send your contact data ... Calculate the number of days between dates ... Confirm that numbers match ... Open a screen when you can't access the "handles" ... Enter data into scattered cells ... Retrieve archived data.
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.
Q. I need to combine the data from two cells and put them into a third cell. I've heard that it can be done, but I don't know how. Can you help?
A. What you want to do, in technical terms, is concatenate the content of two cells into a third--a technique often used in consolidating data. The basic formula, where, for example, 56 is in cell B1 and 78 is in C1 and you want them combined as one number in D1, is
The shorthand formula is
If you want a space between the two numbers, use this formula--placing as many spaces as you wish between the quote marks:
And the shorthand version is
If you have a list of names, with first names in one column and last names in another column, and you want the two names of each person joined in one column with a space between them, use this formula:
But if you want the last name first, separated by a comma, use this formula:
Or this shorthand formula:
For more on this subject, see "Make Excel a Little Smarter" on page 73.
Q. I have two hard disks in my computer and I reserve the D:\ drive exclusively for files I back up. However, my Internet Explorer (IE) is defaulted to place all its temporary files on that drive. Is there a way to make IE save its files to C:\?
A. Fortunately, Internet Explorer has a built-in option to do that. Go to Tools, Internet Options and click on the General tab (see screenshot).
In the middle of the screen, under Temporary Internet files, click on Settings to bring up this screen:
Now click on Move Folder and select the drive from the choices offered.
Th-th-th-that's all folks! That's all there is to it.
Q. I need to send my partner the names, postal addresses, e-mail addresses and phone numbers of many clients. Since all of the information is in my Outlook Contacts file, is there an easy way to send her the data? I'd hate to have to type them.
A. There's no need to retype the data. With a few keystrokes, Contacts can forward everything to your partner.
Here's how to do it: Go to your Contacts folder and highlight all the contacts you want to send to your partner by holding down the Ctrl key and left-clicking on each in turn. When done, click on Forward (shortcut: Ctrl+F).
That will open a new e-mail message with each contact added as an attachment. Address the e-mail to your partner and click on Send.
When your partner receives your e-mail, all she has to do is drag the attachment onto either the Contacts icon in the Outlook Shortcuts bar or directly onto the Contacts folder in the folder list.
Q. When I calculate staff expenses in Excel, I regularly need to calculate how many days between two dates. Is there an easy way to do that?
A. Yes, just use the expression
="end date"-"start date"
So, if you want to know how many days before you can retire to your farm in Indiana on April 30, 2003 and today is January 1, 2003, use this formula:
Be sure to include the quote marks around the dates.
Q. I need to be sure that a number at one end of a large spreadsheet matches another number at the other end.
Since it's tedious going back and forth to check, I wonder if you have a shortcut for making sure they match.
A. Excel has a simple solution. Say one number is in cell A1 and the other is in A2. In the cell where you want the numbers compared, enter this formula:
If A1 and A2 match, the cell with the formula will read TRUE; if they don't, it will display FALSE, as the screenshot at right illustrates.
Q. I was tinkering with my display controls and now, when I open some of my files (it could be a Word or an Excel file), the top of the document is off the screen. As a result I can't reach the title bar and therefore I can't move the document down to where it belongs. Can you help?
A. You have two problems: accessing a document when the title bar is off the screen and fixing what you broke. First I'll tell you how to move the document to the right place because it's a problem that can occur anytime, and the solution is comparable with having an emergency key to your car.
Begin by clicking on the window you want to move; that makes the window active. Then press Alt+Spacebar and then the letter M on the keyboard. Those steps are the same as clicking on the small document icon in the upper-left corner of the window, with M being the shortcut key for Move. Now just use your arrow keys to reposition the window. When finished, press Enter.
To fix the settings problem, click on Start, Control Panel, Display and then Settings. Once there, you can adjust Screen resolution so documents will fit correctly on your monitor screen. You may have to tinker (by sliding the indicator to the right or left) to get the resolution you like.
Q. How do I copy the same information into many different Excel cells when the cells aren't contiguous?
A. Think of the Ctrl key as the repeat key. For example, to easily move a bunch of noncontiguous files in Explorer, hold down the Ctrl key and then select each file. When done, move them in one step. Likewise, in Excel, hold down the Ctrl key as you click on each cell in the set. Then type the information you want to enter. But be careful: Do not press Enter. Instead press Ctrl+Enter.
Q. When my Outlook data file (*.pst) grew too fat for me to easily copy, I archived my data. In fact, I told Outlook to automatically archive my data. So, now that they're all neatly tucked away, how do I access them?
A. You're to be congratulated for archiving your data. Keeping the Outlook data file lean makes for a faster running application.
Before telling you how to open your archive, I'll tell readers how to automatically archive files. In Outlook, go to Tools, Options and then to the Other tab.
In midscreen, click on AutoArchive and Run AutoArchive every, selecting a time period that you're comfortable with. The default is 14 days, which I think is too short. You can extend the period to up to 60 days.
Notice all the other "clean up" steps you can take with the data. Note, too, that you can set custom AutoArchive settings for each Outlook folder.
Now, if you need to check e-mails or calendar items that have been archived, follow these steps. Click on File, Open and Personal Folders File (or it could say, Outlook Data File). From the new screen, select the Archive.pst file and click on OK. That will put Archive Folders at the top of your folder list, as shown below. If you click on the + sign next to Archive Folders, all the folders will be displayed.
By dragging and dropping, you can import the data into a new folder or import data into the original folders. Once you retrieve the data, you can manually move or copy the archived items to other folders as needed.
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 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:||Jul 1, 2003|
|Previous Article:||Deductions for bonus plans: attempting to ensure an accelerated deduction.|
|Next Article:||Montoya recognized for volunteer work. (Inside AICPA).|