Learn Excel Blog

               

Yesterday I mentioned that Chris from New Zealand had a problem using the free Delete External Links utility from Microsoft. I pointed him to a download from a German Excel site. Chris wrote back today with a report on the add-in. Here is his report:

I have tried the German version. It is only 20KB versus the 168KB of the Microsoft version. It is a very simple add-in and doesn’t have the bells and whistles of the other one. However it clears the error message I found with the MS one. It also clears links very simply as well.

How it works:

A dialogue box appears asking what types of files you want to search for links ( such as .xls). You need to be actually in the file to be searched. I tried all sorts of ways of searching for links such as D:*.xls, and C:*.xls when it didn’t appear to clear all links. This showed me all links in a 50 sheet workbook, gave me the option of deleting each one and showed the complete path of each external link one at at time. It showed me links that I didn’t even know existed. It worked efficiently, and on saving the workbook and opening again, all external links are now GONE!!.

There is one flaw in the German add-in. It could not find a series in a chart that referred to an external server ( O:) drive. However, even the MS addin had trouble with this one until I chose the Aggressive mode for charts and then it solved the problem.

Thanks to Chris for reviewing this add-in. If you are having problems getting rid of external links, check out this cool add-in from German Excel guru Hans W. Herber.

               

David asks I read your tip about a macro to save a workbook with a filename found in cell A1. How can I change the name of the worksheet only, with text located in cell A1.

Use this macro:

Public Sub RenameSheet()

NewName = Range(“A1”).Value

ActiveSheet.Name = NewName

End Sub

Chris had downloaded the free Microsoft add-in to find and remove links to other workbooks. It worked fine, but then he started getting an error message: “Routine Location Error Code: 2”. I could not find anything at the Microsoft site, but a German-language Excel message board mentioned a similar problem. Without an answer from Microsoft, it is hard to say why this happens on some computers and not others. If you find that you are experiencing this problem, you should uninstall the add-in on the Tools > Add-ins dialog.

Main wrote to ask if there is a keyboard shortcut for moving to other tabs in a workbook. I always use Ctrl + PgUp to move to a tab to the left of the current worksheet, and Ctrl+Pgdn to move to a tab to the right.

Paul asks, I’ve got for example the following link: =Sheet2!$B$4. Is it possible to make the sheetname (Sheet2) variable e.g. referring to a cell with the name of the sheet?

Paul – the =Indirect() function should help you in this regard. Anywhere that you would normally type a cell reference, you can use the Indirect function. Inside the parentheses, you can use concatenation characters to build a valid cell reference. For example, in cell E4, I typed “Sheet2”. In another cell, I typed this formula:

=Indirect(E4&”!B4″)

The formula correctly returned the contents of cell B4 on Sheet2. The downside of using this method is that since the argument B4 is text, Excel will not update this formula if you ever cut and paste B4 to a new location.

That’s all for today – I’ve got a lot of consulting work to get done today. Sorry to all who I did not have time to answer. If you did not see your question answered, try posting it to the message board – many loyal readers help with questions on the board – it is a great way to get answers quickly.

               

Charlie from Cleveland writes, Assume you are miraculously beating the best team in baseball 2 games to 1 in a best of five series. Your ace pitcher starts to look weary in the 6th inning and then gets in trouble in the 7th inning. Can you show me a formula in Excel to tell if I should pull him out of the game?

Sure, Charlie, it would take a simple If formula. These come in real handy the next time you have a chance to pull off an incredible upset. Try a simple formula like this in cell A1:

=IF(Bartolo=”Weary”,”Absolutely Pull Him Out of the Game”,”Pull Him Out of the Game Anyway”)

Congratulations to all Seattle Mariner fans. Although MrExcel would have enjoyed watching our Wahoos advance to the ALCS, I have to admit that this is Seattle’s year, and it would not have been right for them to have not advanced for a chance at the Pennant. Best of luck in the ALCS.

               

Good Monday Morning!

Since most of the MrExcel readers read the page at work, I’ll start with an introduction here. I was reading the local newspaper and they had an article about the trendy, new, easy-to-use Weblog technology. Basically, it is easy to create a daily posting to your web site, without having to go through the hassles of FTP, etc.

It sounded interesting. On Saturday, I signed up with Blogger, and am now typing this article into my browser. With a click of the button, it will be added to the MrExcel site. I already answer a few e-mails every day, it should be easy to copy and paste them into the Weblog and create a daily Q&A from the MrExcel mailbag.

Sunday was a slow mail day, so here are a couple questions from Friday:

Ricardo asked is there a way to get the date and time automatically by creating a formula?

This is an easy one – use =NOW() to get the current date and time. Use =TODAY() to get the current date.

Carol asks “I have a column of numbers that I want to add. But I want subtotals for each person who may have more than one number. Is there a way to add each individual person’s numbers without selecting each group for each person and manually adding.

Yes, the feature is called Data – Subtotals. You will find the Subtotals command on the Data menu. Select your data, then choose Data – Subtotals. In the Subtotal dialog box, you will answer three questions:

At each change in: Name

Use function: Sum

Add Subtotal to: Amount

For more details and an image about Data Subtotals, read method #4 in Tip 035.

——–

There you have it, weblog entry #3. The downside is that information here will be in a totally random LIFO sequence – one day it may be simple functions, the next day it may be VBA. Add this page to your favorites, check back a every couple of days and let’s see if anything interesting develops from this new technology.

Until tomorrow….

               

From this morning’s mailbag:

I want to add years to a given date. For instance I want to add 5 years to 16th Nov.2001. How can I do that?

There are a lot of cool functions for dealing with dates in Excel. One of the most versatile functions is the =DATE() function. It accepts three arguments, Year, Month, and Day. You can, of course have calculations for those arguments and the function is incredibly flexible. For example, as expected:

=DATE(2001,10,14) will return October 14, 2001.

=DATE(2001,10,14+7) will return October 21, 2001

Here are some more complicated situations that Excel handles with ease:

=DATE(2001,10,14+30) will return November 13, 2001

=DATE(2001,10+6,14) will return April 13, 2002

Three other simple functions for dealing with dates are =YEAR() which will return the year from a given date, =MONTH() which will return the month number of a date, and =DAY() which will return the day of the month of a given date. You can combine all four of these functions together to solve today’s problem. Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date one year later.

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

Bob wrote with a similar problem. He has a column of dates in column A. Each date is the first of the month. Bob would like a formula that would extend the series. You can change the formula to:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

In Bob’s case, you would not need a formula. If he has 1-Jan-2001 in A2 and 1-Feb-2001 in A3, he can highlight A2:A3, then click the fill handle in the lower right corner of the selection, drag down, and the series should correctly extend.

Learn Excel from MrExcel