Monthly Archives: April 2017

#Excel Super Links #19 – shared by David Hager

Excel Tips & Tricks by Rob Bovey

Show and hide a picture (VBA)

Dynamic Segmentation in DAX

Fix: Excel Formulas don’t update in Power Query tables

#Excel: Combining and Refining a Static Filter List & Filter Criteria UDF by David Hager

#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

There was a lot of interest in my post on modifying a shape with a worksheet UDF.

The original idea was posted in 2007. I seem to remember, though, that the use of a UDF to modify cells occurred before that time. The initial discovery was that a UDF could add a cell comment to ANY cell. I can’t find the original reference, but this technique was last documented at:

I have modified the the UDF shown in that article to add a timestamp feature.

Function AddComment(rng As Range, str As String) As String

If Not rng.Comment Is Nothing Then rng.Comment.Delete

TimeStamp = Date & ” ” & Time

If Len(str) Then rng.AddComment.Text str & ” ” & TimeStamp

rng.Comment.Visible = True

End Function

In the example workbook, I entered the AddComment function in cell D6, but the range argument can point to any cell. In fact “range formulas” can also be used.

The INDEX, OFFSET and INDIRECT Excel functions all return ranges, so any formulas built with these functions can be used in a UDF where a range argument is required. The following example uses the INDEX function.

=AddComment(INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)),”MAX value in NumRange”)

where NumRange is defined as =OFFSET(A$1,,,COUNTA($A:$A),) ‘auto-expanding range

In this example, the formula INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)) returns the range of the cell containing the max value of NumRange, and as such it can be used in the first argument of the UDF. So, as numbers are added to column A as shown in the figure


the function will add a timestamped comment to any cell in that range that is the max value.

Obviously, there are numerous and more complex examples that can be built using this technique. I hope that you will find this useful in your projects.

The example file can be downloaded here.


#Excel Super Links #18 – shared by David Hager

The Excel Rollover Mini FAQ

Collating with Excel Formulas from Multiple Sheets based on Conditions

Use any Map with Power BI

The Magic of IF(VALUES()) in Power Pivot

#Excel Shift Calendar by David Hager


Excel Short & Sweet Tip #7(Highlighting External Links) by David Hager

Using conditional formatting to highlight external links has been used before

but it required a VBA solution. Now, with Excel’s new FORMULATEXT function, it can be accomplished using only Excel formulas. So, using the following formula defined as IsExternalLink


Conditional formatting will highlight the cells containing “[“, which is associated with external link formulas. But, a more robust formula can also be used, as shown below.




#Excel Super Links #17 – shared by David Hager

Excel Shapes Information

Modelling Inventory Run Rate & Cash Flows using Excel

Budget Patterns in DAX

Presidential Word Cloud in Power BI

#Excel: Modifying Shapes From An UDF in a Worksheet Cell by David Hager

#Excel Super Links #16 – shared by David Hager

Find cells containing Excel formulas with literal (hard coded) values

New Power BI Reports from a Golden Dataset

Learn Excel – “Show Mean on Bell Curve – 5 Ways”: Podcast #1825B

VLOOKUP: How to calculate 500x faster

Returning the First Temperature in PowerPivot


#Excel Super Links #15 – shared by David Hager

Issue #15 is here!

Which numbers add up to total? Using Only Excel Formulas

Modifying Your List of Recently Viewed Excel Files

Daylight Saving Time And Time Zones In M (Power Query)

Use DAX and Slicers to Define Thresholds for Sentiment Analysis

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function