Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

 

 

Duplicate Number Showing in Excel Tooltip with DDE Formula

 

Richard from the Computer America radio show sent in this interesting problem.

Richard had a worksheet. Strangely, Excel was displaying a tooltip of the value in cell D1. The tooltip was always the same as the number shown in the cell. It seemed totally pointless.

Here is why this is happening. First, the basics. If you attempt to enter a number in a cell and the column is not wide enough for the number, Excel will display ### signs in the cell.

To see the number, you would generally have to use Format > Column > Autofit to make the column wide enough for the number. However, in Excel 2002, Microsoft added a cool feature. You could hover your mouse over the pound signs and a tooltip would appear showing you the number.

I tried to envision a scenario where a formula would start out with a really large number that would display as ### signs, then quickly degrade to a lower number that would fit in the cell. My theory was that Excel would be fooled into displaying a number and also its tooltip. When I dreamt up a formula to demonstrate this, Microsoft handled it very well. These formulas use the =NOW() function to update a number of seconds. I then square the result in D3 to create a suitably large number. Here, the 2 million result is too large for the cell and I get the tooltip.

However, as soon as the number became small enough to fit in the column, the tooltip disappeared.

It turns out that this anomaly only happens with DDE links. Richard was using a third-party tool to bring stock quotes automatically into Excel. The formula in A1:D1 was a DDE formula. Because these are updated automatically WITHOUT Excel doing a recalc, the logic inside of Excel was not smart enough to detect that the number had become small enough to appear in the column width. One interesting workaround: if Richard uses Format > Cells > Alignment > Shrink to Fit, the tooltip permanently disappears, even if the number becomes too large and the ###'s are displayed. We still aren't sure about the exact combination of widths, but as Richard says, we've "narrowed" it down!

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.