#Excel Short and Sweet Tip #12 (Documenting Formulas and Highlighting Those Formulas With Conditional Formatting) by David Hager

One way to document a worksheet cell is through a cell comment. That was demonstrated recently by the following article.

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

However, there is another way to add a comment to a cell containing a formula. The N function returns a value of zero if the argument is a string (i.e. – =N(“Hi”) equals zero), so it does not affect the value of the main formula.

So, the formula to add the comment to is:

=MATCH(TRUE,ISNUMBER(FIND(“s”,$A$1:$A$20)),0)

which is an interesting formula in its own right but a discussion of it is not germane to this article.

This is the formula with the comment attached.

=MATCH(TRUE,ISNUMBER(FIND(“s”,$A$1:$A$20)),0)+N(“This formula created 05/11/17 by me.”)

In order to locate formulas of this type, a special conditional formatting formula is needed.

The formula needed to create the conditional format for cells containing formulas & documented by the N function is shown below (defined with D5 as the active cell).

HasFormulaComment=FIND(“+N(“””,FORMULATEXT(D5))

A notable feature of this formula is that since a quote is to be part of the lookup string +N(“ then a triple quote is required by Excel at the end of the string.

In the figure below, this conditional fomat is applied to column D.

 ComCF1

For D5, this formula returns a value of 47. If it returns an error or 0, then the CF condition is FALSE. So, only cell D5 in column D is highlighted.

You can download the example file here.

Comment_ConditionalFormat

 

Advertisements

3 thoughts on “#Excel Short and Sweet Tip #12 (Documenting Formulas and Highlighting Those Formulas With Conditional Formatting) by David Hager

  1. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel Super Links #37 – shared by David Hager | Excel For You

  3. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s