Monthly Archives: April 2017

#Excel Super Links #19 – shared by David Hager

Excel Tips & Tricks by Rob Bovey

http://appspro.com/Tips/Tips_And_Tricks.htm

Show and hide a picture (VBA)

http://www.get-digital-help.com/2013/10/25/show-and-hide-a-picture-vba/

Dynamic Segmentation in DAX

http://www.daxpatterns.com/dynamic-segmentation/

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

http://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/

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

https://dhexcel1.wordpress.com/2017/04/15/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.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

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:

http://www.listendata.com/2013/04/excel-udf-dependent-cell-comment.html

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

addcomment1

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.

AddComment

#Excel Super Links #18 – shared by David Hager

The Excel Rollover Mini FAQ

https://optionexplicitvba.com/2012/09/17/the-excel-rollover-mini-faq/

Collating with Excel Formulas from Multiple Sheets based on Conditions

https://excelxor.com/2014/10/16/collating-from-multiple-sheets-based-on-conditions/

Use any Map with Power BI

https://www.kasperonbi.com/use-any-map-with-power-bi/

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

https://powerpivotpro.com/2011/03/the-magic-of-ifvalues/

#Excel Shift Calendar by David Hager

https://dhexcel1.wordpress.com/2017/04/03/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

https://www.extendoffice.com/documents/excel/1539-excel-highlight-external-links.html

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

=FIND(“[“,FORMULATEXT(D10))

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.

=FIND(“[“,FORMULATEXT(D10))+FIND(“]”,FORMULATEXT(D10))

cfextlink

 

#Excel Super Links #17 – shared by David Hager

Excel Shapes Information

http://dmcritchie.mvps.org/excel/shapes.htm

Modelling Inventory Run Rate & Cash Flows using Excel

http://chandoo.org/wp/2017/04/19/model-inventory-cash-flows-excel/

Budget Patterns in DAX

http://www.daxpatterns.com/budget-patterns/

Presidential Word Cloud in Power BI

http://datachant.com/2016/04/02/presidential-word-cloud-in-power-bi/

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

https://dhexcel1.wordpress.com/2017/04/19/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

http://www.get-digital-help.com/2015/01/19/find-cells-containing-formulas-with-literal-hard-coded-values/

New Power BI Reports from a Golden Dataset

http://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/

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

https://youtu.be/bJpUpnXBT2o

VLOOKUP: How to calculate 500x faster

https://exceloffthegrid.com/vlookup-how-to-calculate-faster/

Returning the First Temperature in PowerPivot

https://dhexcel1.wordpress.com/2014/05/25/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

https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/

Modifying Your List of Recently Viewed Excel Files

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-modifying-your-list-of-recently-viewed-files/

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

https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/

Use DAX and Slicers to Define Thresholds for Sentiment Analysis

http://datachant.com/2016/09/06/use-dax-slicers-sentiment-analysis/

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

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/