Excel Question #46
 

Is there a way to tell Excel to stop turning email addresses in Excel databases into hyperlinks? We keep (small) databases in Excel and when we enter an email address, it automatically turns it into a hyperlink.

Previously, I just ignored this, but ran into an issue today where someone who I gave the database to so that they could email things for me was just clicking on the hyperlinked email address to address the email. The problem is that on a few records, when an email address was changed (usually because of personnel change for that record), the hyperlink did not automatically update.

I was able to do a mass change to ensure that the problem does not occur for the list as it exists right now, but I am searching for a long term solution.

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.

Awesome. I had the same prob. It can also be done in Word as well. Yay!

Thank you. I looked everywhere except in Autocorrect settings. I couldn't find anything in help either.

Pretty much any and all formatting that Word/Excel do automatically can be controlled through AutoCorrect.

AutoCorrect is a feature much reviled by light users of the application (and justifiably so for some of the default settings) but is among one of the most powerful tools for advanced users. Just a mild example but currently I have reason to type this phrase a lot: HL/CL Statement Suppression Q1 2009 - Project #XXXXX. And it has to be in that form. Because of AutoCorrect and a little bit of acclimation I just type /hl and it automatically gets transformed into the correct phrase. I have another one for my full signature block. I know some people who have turned Word into a shorthand machine their AutoCorrect list is so extensive and their comfort in typing that way so practiced.

I have a few things I have "programmed" in Word to do that. I agree that it is a great tool!

Rather than starting yet another Excel help thread, I drudged up an old one.

If I have a series of cells that are calculation results, is there a way to get a count of just the cells with a result value of greater than zero (or conversely, those cells with result value of zero)

I tried using DCountA, but that didn't work (unless I wrote the formula incorrectly).

Any ideas


ETA: I found my own answer: a CountIF function

Dammit, how can I look brilliant if you go and answer your own questions?

It took me 45 minutes of searching before I found it: how long would it have taken for you to provide the answer?