Hello everyone. Sorry for the lack of tips lately. A crush of end-of-the-year projects, plus having the kids out of school for the holiday has meant a lack of time. Look for the tips to return Jan 2nd.

Linda sent in today's question: One of my Outlook contacts is incorrectly set to send e-mail to the wrong address. I can change it, but the mail sent to the MyTeam distribution list still goes to the old address. Why?

Interesting problem. I wonder why Microsoft does not update the distribution lists automatically, but they don't.

Follow these steps, first to change the e-mail address and then update the distribution list.

• In Outlook, make sure the folder pane is visible by selecting View from the menu, wait 3-5 seconds for the menu to expand, then see if there is a box around the icon next to Folder View. If there is not, then select Folder View.
• You will not be able to see a list of personal folders on the left side of your Outlook Window. If necessary, press the plus sign next to your Personal Folders. Look for the folder called "Contacts" in the folder pane on the left side of the Outlook Window. Click Contacts.
• In the right side of your Outlook window, you will see a list of all your Contacts. If necessary, use the scroll bar along the side or the bottom to find the entry for Cheryl N. Double-click on her name to open the contact record for Cheryl.
• In the contact record for Cheryl, on the 2nd column, 5th row, click in the e-mail box, to the right of the existing e-mail address. Use the backspace key once and the wrong e-mail will disappear. Now, retype the correct e-mail (making sure to use @MyExcel.com instead of @MrExcel.com). Tab out of the field and you should see the e-mail address change in the "Display As" field just below the e-mail field. Both the E-Mail and the Display As field should show the correct address.
• Your changes are not correct until you press the Save and Close icon in the upper left corner.
• Finally, and the most important step, if you have any Distribution Lists set up, you need to open this distribution list in the Contacts folder, and click the Update Now button on the right side of the dialog box. This will update the distribution list with the corrected e-mail.

I am going to repeat a cool tip from the Message Board for today's tip. Pat asked: Is there a quick and easy way to format cells to indicate which ones have formulas and which have values? I know you can display all formulas, but I want to look at the spreadsheet and easily tell
which are calculated values and which are entered values.

Juan Pablo, the Excel whiz behind our Spanish-language MrExcel Consultoria Division, came up with this cool trick that relies on XLM:
One *quick* method to do this is as follows.

Select A1.
Go to Insert, Names, Define.
Put a name in it, like HasForm
in the refers to put:
=GET.CELL(48,A1)

Now, select the range you want to "check" for formulas. Select Conditional Formatting, change Value is to Formula is, and put this formula

=HasForm

Click on the pattern button and select something that makes it easy for you to see it has a formula and you're ready to go !!

Tony wrote with a question that looks simple, but requires a fairly complicated formula: I'm running excel 97 and I want some of the date formated cells in column 'K' to return a date that is the end of the month when I enter any day of that month in 'G'. I've tried EMONTH from a early version of excel but have not got it to work in this version.

There are a few ways to solve many problems in Excel. I am sure someone will come up with something simpler, but at the moment, this formula comes to mind:

=DATE(YEAR(E1),1+MONTH(E1),1)-1

The Date() function generally accepts three arguments: A year, a month, and a day. So, December 13 2001 could be entered as =DATE(2001,12,13).

The date function is also fairly forgiving. If you specify an invalid number of months, such as 13, it will give the correct date. =DATE(2001,13,1) will be January 1, 2002. This makes it a great function when you need to add or subtract to the current date.

In solving this problem, it is easier to find the first day of the next month and then subtract one day. This is easier, because we know the day portion of the first of next month is always 1. Whereas, if you tried to find the last day of this month, you would have to check for leap years, etc - it would generally be a mess.

If you have a date in E1, then Year(E1) will return the year portion of the date. Month(E1)+1 will return the month portion of the date and advance it to the next month. The final argument, the 1 will select the first day of the next month. Then, finally, I subtract one from that date to convert 1/1/2002 to 12/31/2001.

Update: Thanks to Steve H. for this out-of-the-box answer, which will also work:
=DATE(YEAR(E1),1+MONTH(E1),0)
It is unusual to consider the "0th" of a month, but it works!

Several quick answers today:

Jeff: I have figured out how to add Hour:Min, but now that I have that total - How do I multiply that times Dollars per hour?
If the total is in A10, and dollars per hour is in A11, then use =A10*24*A11

Richard: I have a macro to sort a worksheet. It works when the sheet is unprotected, but not when it is protected.
In your macro, unprotect the sheet, do the sort, then protect the sheet. Provided your sort routine does not crash, there is no danger that the user will be left with an unprotected sheet.

Chiles: Where can I download Excel
You can't. It comes on a couple of CD's. It is too big to download. You can download a competitive product called Star Office from http://www.sun.com/staroffice/

Robbie: I am having problems with relational data bases. could send me some tips to do them. Also a easy method saying what they are and how i carry then out on the computer.
Excel is not good at doing relational databases. Access is the product to use.

Last night, I delivered a presentation on Advanced Excel Tips and Tricks to the Akron & Canton chapters of the Institute of Manageral Accountants. This was a great group of very fluent Excel users. Here is some of the Q&A from that session.

If I enter a formula to take one-third of a number like 100,000 - how can I round that result to get just the whole number portion, or even just the thousands? I know I can format it to hide the decimals, but I don't want the decimals to enter into subsequent calculations?

Surround the formula with the =ROUND() function. If the formula is currently A2/3, then use this formula to round to the nearest whole number:
=ROUND(A2/3,0)
The ",0" in the formula says that you want to round to have no digits to the right of the decimal place. If you wanted to have 2 decimal places, then use
=ROUND(A2/3,2)
To round to the nearest thousand, indicate a negative number as the 2nd argument for the round function
=ROUND(A2/3,-3) will round to the nearest 1000.

How can I get rid of broken links after receiving an e-mailed workbook?
There are many causes of broken links. A great place to start to look is in the Insert - Names - Define dialog box. Many named ranges may be pointing to the linked workbook. Simply delete these unused names. Another place that links may hide is in the Data - Consolidate dialog box. If you use Data Consolidate, you may find old entries hanging out in this dialog box may be the source of the unwanted link.

Microsoft offers a utility to delete the broken links, but the best utility we have found is the FindLink add-in from Hans Herber, the German version of MrExcel. If you read German, check out this page, http://www.excel-cd.de/forum/archiv/24to28/t24746.htm, where you can download http://www.herber.de/andere/findlink.zip.

We have three computers in our office. During the course of the day, each computer will have an occassion to open a particular workbook on the network. Frequently, we find that after someone has accessed the workbook, the formatting becomes lost. When the next person opens the workbook, the numbers do not fit in the columns, everything shows as ######, etc. What could be causing this?
And for this one, I will turn to the readers of MrExcel. Any ideas? If you have experienced similar problems and can suggest a solution, drop an e-mail to challenge@MrExcel.com. The best answers will be rewarded with one of the highly collectible MrExcel.com Salt Lake City 2002 Winter Olympic Calendars.

Thanks to the board of the Akron IMA chapter for inviting me to speak at their professional development evening. It was great to have a fantastic turnout and large audience for the presentation!

Steven from Australia writes: I have created a VBA macro which has to create about 50 Charts from one worksheet. The problem is that everytime I run the program when I get to the 33rd chart an error message displays "Not enough memory", then the Excel program locks up and I have to terminate the program. I have 256Meg of RAM in my PC and I'm using Excel 97 in a Windows NT operating system.

Are you creating each chart as its own chart sheet? Excel can handle 16 million cells on a worksheet, but the quiet secret is that is can not handle a lot of worksheets. The help file says that the number of worksheets is limited by "available memory".

I regularly experience the problem that you have. It is horribly frustrating, because you never know when it is about to crash. If Visual Basic would give you a trappable error, you could stop the macro, save the file, start in a new file. But they don't - you just get a crash.

I have seen the crash happen as late as 130 worksheets and as early as 40. You have to gauge where it is going to crash in your system, then put a counter in the macro. If you think you are going to crash after 32 charts, then stop the process at 30 charts, save them in a new workbook, close that workbook, and start creating them again in a new workbook.

This isn't pretty, but it is the only workaround that I have found.

Another thought - make sure that you close each module and userform in the Visual Basic Editor using the "X" in the upper right corner. I have found that by simply closing all of the components in Visual Basic before running the macro, you can free up a bit more memory and possibly squeeze a few more charts into the "available memory".

Above, I talked about doing things to conserve memory. Steven wrote back today with an excellent discovery - I found that if I set the Charts AutoScaleFont to False, I could create about 120 chart, which has solved my problem. Why this is so I have no idea, but thats Excel. Excellent tip - tuck this one away as an obscure method for conserving memory.

Geri wrote with today's question: How can I copy a sheet that has been subtotaled and then condensed to totals, to another sheet without all of the underlying detail?

Great question. When you use the Group and Outline buttons (or even the AutoFilter) to hide certain rows and then try to copy them, you find that Excel copies all of the hidden rows as well. Luckily, there is an easy, if not intuitive, solution to the problem.

First, highlight all of the totals like you normally would.

Then, from the menu, choose Edit > GoTo. Click the Special button to open up the GoTo Special dialog box. In this dialog box, select Visible Cells Only from the right column.

You can now use Ctrl+C or Edit - Copy to copy the subtotals, then use Edit > PasteSpecial > Values to paste them in a new location.

This was one of the original Tips of the Week - you can check out an image of the dialog box at http://www.mrexcel.com/tip003.shtml