#Excel Short and Sweet Tip #19 (Invaluable Excel Speller) by David Hager

Do you know about those kids toys that spells out the name of an object and then says the word? Well, you can do the same thing in Excel. This article is about the use of Speech.Speak in VBA, and this process provides a good demonstration of its use. Place this event procedure in a Worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

textInCell = Target.Value

If textInCell = “” Then Exit Sub

For n = 1 To Len(textInCell)

If Asc(Mid(textInCell, n, 1)) > 64 And Asc(Mid(textInCell, n, 1)) < 91 Then

Application.Speech.Speak “Capital ” & Mid(textInCell, n, 1)

Else

Application.Speech.Speak Mid(textInCell, n, 1)

End If

Next

Application.Speech.Speak (“spells ” & textInCell)

End Sub

Then, select a cell on the worksheet and if it contains a word it will spell the word and then say it. In the example file, A1 contains the word “Elephant”. If that cell is selected, it will be spelled out starting with “capital E” followed by the rest of the letters and then says “spells Elephant”.

You can download the file here. It also contains a greeting upon opening the workbook.

Welcome

#Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet) by David Hager

You might at some point want to view a section of a workbook without any user-applied formatting. It turns out that this is easy to do through conditional formatting. For this example, A4:G10 has data that has a simple table format applied, as shown below.

 

There is a Data Validation list option in cell B2, with the list in A1:A2 (On, Off). Then, a conditional format is applied to A4:G10 with the formula =IF($B$2=”Off”,1,0).

 

The important part of this process is selecting formatting options that look “normal”, such as no fill, no borders and a black font. When the Off value is selected, the formatting of the table “disappears”, as shown below.

 

You can download the example file here.

ToggleFormatting

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

Most of the published methods that convert a numeric grade to a letter grade either use a lookup table or do not account for + or – scores. The following formula overcomes both of those limitations. For the following formula, the numeric grade is in cell A5.

=IF(A5>=90,”A”,IF(A5>=80,”B”,IF(A5>=70,”C”,IF(A5>=60,”D”,”F”))))&IF(A5>98,”+”,IF(A5<59,””,IF(OR(RIGHT(A5,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(A5,1)={“8″,”9″}),”+”,””))))

 

#Excel: Project Tracking Workbook by David Hager

This workbook is one of many Excel projects I have worked on over the years. I am sharing it here for the first time. I am not going to discuss how this application works but rather offer it up to the user as both a useful tool and an opportunity to explore the formulas used in its construction. It is important to note that the workbook is completely unprotected and contains no VBA code.
The ProjSetup worksheet is for data input. Column A is for the names of the projects to track. Column B is for the starting date of the project and Column C is for the finish date of the project. Column D is the priority of the project – low, medium or high. A Data Validation list allows for those values to be selected, and conditional formatting determines the color. Column F has an option box to select between days and weeks. The following figure shows these features.

The ProjTimeDisplay worksheet shows the project time schedules. This was constructed with Excel formulas and conditional formatting. Feel free to dig in to see how it works. Here is a figure to visualize the output.

The project workbook can be downloaded here.

xlProj

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

it shows an example at the bottom of the article where the 1^st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:

=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).

As you can see in the figure, each record appears to be on a single line in the cell (F9).

This same technique can be enhanced further in this way.

=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula

=CHAR(10)&REPT(“-“,CELL(“width”,H9)+NOW()*0)&CHAR(10).

With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.

The example file can be downloaded here.

TJ_MultDelimiters

#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

In a conversation with Bill Jelen about the TEXTJOIN function, he mentioned whether I had tried using 3D references as arguments. I said that I had not tried native 3D ranges, and I did not believe it would work. Well, I was wrong (again). It does work, in spite of there being no documentation from Microsoft about that ability. The figure below shows the result in cell D2 of using this formula:

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2)

I continue to be amazed by the myriad of formula solutions made possible by the TEXTJOIN function. In a recent article where I demonstrated the use of non-contiguous ranges

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

I now realize that the same concept can be used with 3D ranges. (Here is the bonus!) In fact, that 3D range can be in ANOTHER workbook, as shown below.

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2,[TEXTJOIN_Native3DB.xls]Sheet1:Sheet2!A1:A2)

The example file can be downloaded here (Note: I did not include the external file, and I put a tilde in front of that formula)

TEXTJOIN_Native3D

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

#Excel UDF Using Google API to Return the Elevation of an Address by David Hager

There has been a lot of interest in using the Google API in Excel VBA for geocoding.

This article demonstrated a method of returning latitude and longitude coordinates from an address

http://analystcave.com/excel-get-geolocation-coordinates-of-an-address/

and this article showed how to return an elevation from latitude and longitude coordinates.

http://oco-carbon.com/coding/altitude-in-excel-google-elevation-api/

So, I decided to combine the techniques from both articles to create an UDF that would use an address to give the elevation of that location. The first line of code illustrates the function arguments.

Function ElevationFromAddress(address As String, Optional ToFeet As Boolean) As Double

An address string is the 1^st argument and there is an optional 2^nd boolean argument for coverting meters to feet. Please feel free to download the example file containing the complete code for the UDF

The Google API algorithm can recognize locations with limited or detailed address information. The first two records in the table are for locations at a considerable altitude. The next record is for a Baptist seminary in Fort Worth, Tx. The last records are both for The White House in Washington, D.C. Note that the values are different – the first record must be for the front gate while the generic address refers to the actual building. As shown in the figure, the formulas in column B use the 2^nd optional argument to return the elevation in feet.

This function procedure requires a reference to Microsoft XML, v6.0, selected in the VBE under Tools, References, as shown here.

The file can be downloaded here.

ElevationFromAddress

#Excel: Creating a List of Option Expiration Dates and Triple Witching Dates with Excel Formulas by David Hager

Those people that closely follow the workings of the U.S. financial market know that options expiration day is important. In particular, triple-witching refers to the quarterly expiration of index futures, index future options and certain stock options on the third Friday of March, June, September and December. The other months having a 3^rd Friday are also important option expiration dates.

=TODAY() ‘in cell A1

=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40))=5),0))+1 ‘formula in A2 and fill down.

This will afford the list of option expiration dates. In order to see triple witching option expiration dates, this conditional formatting formula must be used on the date list.

Triple=NOT(MOD(MONTH(A2),3)) ‘defined when active cell is A2.

For the first cell, a different conditional formatting formula is needed. With A1 as the active cell, define Start:

=OR(A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(

WEEKDAY(A1+ROW($1:$40),11)=5),0))-28, A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW

($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40),11)=5),0))-35)

When the conditional format is applied to cell A1, the characters will appear as bold purple in the model when that date is an options expiration date. In order to see triple witching option expiration dates in A1, this additional conditional formatting formula was used to hightlight bold red text if A1 contains a triple witching date.

=AND(Triple,Start)

You can download the file here.

OptionExpire

 

#Excel Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

you will find that there are multiple text arguments that are optional for TEXTJOIN. They have the same properties as the 3^rd argument, which allows for an array of strings. So, the following formula has 3 non-contiguous ranges as its last 3 arguments.

=TEXTJOIN(“,”,TRUE,A1:A10,C1:C10,E1:E10)

which affords

a,b,c,a,b,c,a,b,c,a,c,d,e,c,d,e,c,d,e,c,e,f,g,e,f,g,e,f,g,e

 

Note that this comma delimited string obtained from the TEXTJOIN function is the starting point of the technique demonstrated in this article.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, for example, you can use non-contiguous ranges as the starting point for making an unique array or unique delimited string. Pretty neat and powerful stuff!

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

 

Copy #Excel Chart as a Enhanced Metafile Picture by David Hager

I am sharing here yet another great Excel technique from Rob van Gelder. This procedure allows you to click a chart in any open workbook and run the macro to convert the chart to a .emf file at the location of your choice. See:

http://dailydoseofexcel.com/archives/2012/05/05/copy-chart-as-a-picture/#comments

The code from that article is shown here.

Declare Function OpenClipboard Lib “user32” (ByVal hwnd As Long) As Long

Declare Function CloseClipboard Lib “user32” () As Long

Declare Function GetClipboardData Lib “user32” (ByVal wFormat As Long) As Long

Declare Function EmptyClipboard Lib “user32” () As Long

Declare Function CopyEnhMetaFileA Lib “gdi32” (ByVal hENHSrc As Long, ByVal lpszFile As String) As Long

Declare Function DeleteEnhMetaFile Lib “gdi32” (ByVal hemf As Long) As Long

Const CF_ENHMETAFILE As Long = 14

Const cInitialFilename = “Picture1.emf”

Const cFileFilter = “Enhanced Windows Metafile (*.emf), *.emf”

 

Public Sub SaveAsEMF()

Dim var As Variant, lng As Long

 

var = Application.GetSaveAsFilename(cInitialFilename, cFileFilter)

If VarType(var) <> vbBoolean Then

On Error Resume Next

Selection.Copy

 

OpenClipboard 0

lng = GetClipboardData(CF_ENHMETAFILE)

lng = CopyEnhMetaFileA(lng, var)

EmptyClipboard

CloseClipboard

DeleteEnhMetaFile lng

On Error GoTo 0

End If

End Sub

You can download the file containing this procedure here.

CopyChartAsPicture

Important note: This solution will run correctly only on 32-bit systems – not 64-bit. If you really need it to work on 64-bit, you can try to modify the code based on the information located at:

http://www.jkp-ads.com/articles/apideclarations.asp

Generating Random Initials with DAX in Power BI by David Hager

After publishing a short article about generating random initials in Excel

https://dhexcel1.wordpress.com/2017/05/18/excel-short-and-sweet-tip-14-generating-random-initials-by-david-hager/

I wanted to see if the same formula worked in Power BI. When I entered the aforementioned formula into a calculated column in Power BI desktop, I got an error message. After some research, I discovered that was no CHAR function in the DAX function reference. Then, I remembered that Chris Webb showed examples of the new UNICHAR function at the following link.

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-data-visualisation/

And, Chris discovered that the UNICHAR function was completely undocumented and was probably introduced in the April 2017 update. So, I changed my random initials formula using UNICHAR instead of CHAR, and it worked.

=UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)

Now, you can use this in your Power BI projects.

It is still a mystery how DAX could be around for 8 years and not have the CHAR function (or its equivalent).

 

#Excel Short and Sweet Tip #14 (Generating Random Initials) by David Hager

Sometimes in the construction of a spreadsheet model, you may need to fill a range with something to represent people. Here is a formula that you can use to generate random initials.

=CHAR(INT(RAND()*26)+65)&CHAR(INT(RAND()*26)+65)&CHAR(INT(RAND()*26)+65)

Type this formula into a cell and copy it by using the fill handle into the # of cells desired. These initials will change each time the spreadsheet is recalculated unless you freeze the values. This can be done by selecting the range containing the initials and choosing EDIT || COPY, then EDIT || PASTE SPECIAL.Click the values option and press Enter.

This formula does not calculate as (I) expected. In my experience, the use of the RAND function in a formula can only return one random number. However, in this case, Excel apparently calculates each of the 3 parts of the formula as a distinct formula.

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

Control charts are common in all types of working environments. Although the process of making a control chart is straightforward, the collected data used in the chart sometimes has outliers. For outliers, a determination of whether they have a special cause or are part of the process variation is needed. In either case, many times the decision is made to leave the outliers in the data set. However, these data points should not be used to calculate the statistical variation. A typical example is a column of collected data (in this example defined as range). A normal calculation of the mean and the variation is shown by the following formulas.

=AVERAGE(range) ‘returns 109.6014

=STDEVP(range) ‘returns 6.42546

These values can be used to create the statistical deviations needed in the chart. However, it is more desirable to recalculate those values based on a dataset without outliers. This is accomplished by the following array formulas.

=AVERAGE(IF((range<(AVERAGE(range)+(STDEVP(range)*3)))*(range>(AVERAGE(range)-(STDEVP(range)*3)))*range=0,””,range)) ‘returns 109.4274

=STDEVP(IF((range<(AVERAGE(range)+(STDEVP(range)*3)))*(range>(AVERAGE(range)-(STDEVP(range)*3)))*range=0,””,range)) ‘returns 6.09267

The formulas needed to construct the control chart are typified by the following, which returns minus 3 deviations (in column I in the example worksheet).

=CHOOSE(Which,$E$2-3*$E$3,$F$2-3*$F$3)

where Which is the name of the cell (H2) containing a data validation dropdown with a list consisting of 1 and 2 and the two choices are the calculation for raw data and modified data respectively. So, there are different formulas in C6:I6 that are filled down to create the dataset used to make the control chart shown in the figure.

 

I hope that you found the techniques used here informative. But, the best part of this is that you can use your data (defined as range) to visualize your data from both a raw and modified perspective in the control chart.

Note: you can turn the calculations in your control chart data set to values if you have no further plan to add data later.

You can download the control chart example file here.

ModifyStdData

 

#Excel For You Blog Posts Containing Downloadable Workbook Files by David Hager

For those that find it difficult to locate blog posts on this site with example files, I have generated a Word file with a list of all Excel For You posts that have downloadable example files. For convenience, you can download the Word file here.

Excel For You Blog Posts With Files Thru 051617

I hope that this makes it easier for you to locate the content you are interested in.

#Excel Short and Sweep Tip #13 (KeyBoard DFAROT Unique Technique) by David Hager

Using Data Filter Advanced Filter to obtain a list of unique items from another list is quick and relatively easy to do. However, when Rob van Gelder came up with a keystroke way to do this rather by clicking, it made this a lot easier to accomplish.

http://dailydoseofexcel.com/archives/2012/05/25/copy-unique-values/

as per his instructions:

Select the range to extract from

Hold down the Alt key

Press these keys in sequence: d, f, a, r, o, t

Release the Alt key

Select the range to paste the unique values to.

The only caveat to this approach that I found is that when Excel does not recognize that the selection has headers, it will stop at an intermediary stage. Then, you can click the appropriate options to finish the unique items copy.

HTH!

#Excel: Origin of Sparklines – LineChart VBA User-Defined Function by David Hager

A number of Excel features added to the product by Microsoft over the years were originally made by Excel developers. The addition of sparklines added a whole new way to visualize data. Before they were an Excel feature, sparklines were popularized by Edward Tufte. See:

https://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=000AI

The inspiration for the implementation of sparklines in Excel actually came from Excel developer Rob van Gelder. In this article

http://dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

he shares the VBA code for the LineChart user-defined function, which places a chart in the cell where the function is called from. You can view the use of this UDF and the VBA code in this example file.

 UDFLineChart

If you are not familiar with Excel’s built-in sparkline feature, you can read the following Microsoft article.

https://support.office.com/en-us/article/Use-sparklines-to-show-data-trends-1474E169-008C-4783-926B-5C60E620F5CA

 

#Excel: Finding and Visualizing the Last Record in a Table Based on Criteria by David Hager

A tweet by Tom Urtis intrigued me.

https://twitter.com/TomUrtis/status/863092107564638208

I used to play with the 3^rd argument of the MATCH function many years ago, but I gave it up as a lost cause because strange results were returned if the column in question had blank cells in it. However, if used in a contiguous list, Tom demonstrated its utility in the following formula.

=MATCH(2,1/(Table1[Name]=”Urtis”))

If the 3^rd argument of the MATCH is not declared, it defaults to a value of 1. That allows for a lookup that is equal to or less than the 1^st argument. The undocumented feature of the 3^rd argument is that when the value is 1 it does the lookup from the bottom of the data rather than the top. So, Tom’s formula finds the LAST matching item (in cell A44 in the example workbook).

I realized that this technique could be extended to multiple criteria. The following formula shows 2 criteria

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)))

and this formula shows 3 criteria.

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))

It is important to note that the criteria can be from any column in a table, and just not adjacent rows.

Note also that the formulas return the row position in the table.

This technique can also be used for visualizing the row matching the criteria by conditional formatting (CF).

This is the conditional formatting formula used for 3 criteria.

Criteria3=ROW()=ROW(INDIRECT(“A”&MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))+1))

This CF formula is applied to the entire table and highlights row 21 as expected.

As a further extension of this technique, a criteria table can be used that replaces the static criteria with values from the table, as shown below.

=MATCH(2,1/(Table1[Name]=F2))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)*(Table1[Total]<H2)))

So, there are a number of ways to use this technique. Thanks Tom, for the idea.

You can download the file here.

LastRowCrit

#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.

 

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

 

#Excel: Creating a Environmental Variables Table with the VBA ENVIRON Function UDF by David Hager

Is it useful to obtain information about the various aspects of your PC operating system. This can easily be done with the VBA ENVIRON function. Since it is a VBA function, it cannot be used directly in a worksheet cell. The following UDF encapsulates the ENVIRON function, and this function can be used on the worksheet.

Function Env(Lposition As Variant)

Env = Environ(LPosition)

End Function

It is important to note that the variable Lposition is declared as a Variant so that both numeric and built-in Excel strings can be used by this UDF. I could not find a list of the strings on the net, but in this example I will show you how to make a list. The values listed below are for my computer.

A1: =Env(ROW()) returns ALLUSERSPROFILE=C:\ProgramData

B1: =LEFT(A1,FIND(“=”,A1)-1) returns ALLUSERSPROFILE

C1: =Env(B1) returns C:\ProgramData

Note that in B1 is the built-in string argument used by C1.

You can download the file here.

Envir

When you do, fill A1:C1 down to row 40 on the worksheet to make the environmental variable table for your computer. Column B will contain the built-in strings. I hope that you find this useful.

#Excel: Building a Frequency Summary Table Based on an Excel List by David Hager

After publishing a tip on the most frequent item in a list

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

I decided to build a model based on frequency.

The first formula used is the aforementioned most frequent item formila.

=INDEX(ListRange,MODE(MATCH(ListRange,ListRange,0)))

where ListRange = A2:A16

Then, another formula was needed to return subsequent frequent items,

=INDEX(ListRange,MODE(IF(COUNTIF($E$2:E2,ListRange)=0,MATCH(ListRange,ListRange,0))))

entered into cell E3 and copied down.

The formula to return the number for each item was entered in F2 and copied down.

=COUNTIF(ListRange,E2)

The amount spent on each transaction type was calculated the formula placed in G2 and copied down.

=SUM(IF(ListRange=E2,Total,0))

where Total = B2:B16

Finally, information can be used columns F & G to make a formula that will return the average for each transaction type.

=ROUND(G2/F2,2)

The finished table is shown in the following figure.

 

The frequency table file can be downloaded here.

FreqItems

 

#Excel Short and Sweep Tip #11 (Most Frequent Item in Column) by David Hager

Have you ever had the need to return the most frequent item from a column range? The item can either be a number or a string. To do this, use the following formula.

=INDEX(ListRange,MODE(MATCH(ListRange,ListRange,0)))

where ListRange is a single column range.

 

#Excel: A Model Using the CONVERT Function Containing Categories by David Hager

The CONVERT function in Excel allows the user to calculate from one type of unit to another. When you get to the 2^nd argument in writing that formula, Excel provides an intellisense dropdown for the selection of units.

Unfortunately, although the units are in category order, there is no way to see a category description.

The tables containing the units used by the CONVERT function can be found at the following link.

https://support.office.com/en-us/article/CONVERT-function-D785BEF1-808E-4AAC-BDCD-666C810F9AF2

That data was imported, messaged and cleansed to make the list found in the Info worksheet, adding a category for each record in the list. I then used the Advanced Filter to make a list of unique items from column A. That is the primary list used in the calculation model (on the Convert worksheet).

Type=Convert!$C$2

And is used as a data validation dropdown in

Category=Convert!$C$2

The workbook also provides a good example of creating dependent dropdowns from a list. A great formula used to do this is shown below:

CategoryDep=INDIRECT(“Info!C”&MATCH(Category,Info!$A$1:$A$93,0)&”:C”&MAX(IF(Category=Info!$A$2:$A$93,ROW(Info!$A$2:$A$93))))

This data validation list formula is used in the dropdowns in E2 and F2.

From =Convert!$E$2

To =Convert!$F$2

In the example workbook, the cells that are formatted as light blue have Data Validation dropdowns that allow categories to be used with the unit selections for the CONVERT function. So, the dropdown in C2 is used to select the category, and the dropdowns in E2 and F2 are used as the 2^nd and 3^rd arguments in the CONVERT function in cell A2.

Note that this technique does not include the Prefix and Binary Prefix categories.

It is also important to note that since the unit information used here is in a worksheet list, we are not bound by the examples provided by Microsoft. However, a different calculation mode would have to be used, since the custom unit symbols would not be recognized by the CONVERT function. I plan to look at using a formula like =PRODUCT(number,multiplier) for the custom categories. Feel free to use this idea to build your own custom examples. I will be working on it, too 😊.

You can download the file here.

convert2

#Excel Magic Consolidator by David Hager

I made this a long time ago. However, you still might find it useful, if only for the examples of the formulas used.

There are no examples of the formulas referred to in this text in the working xl file, but you should be able to construct your own, based on the following information. Be aware that this methodology uses xlm in defined name formulas. Therefore, do not change the file type. Read all instructions before using.

You can download the instruction document and the working file here.

MagicCons document     MagicCons xl file

#Excel Short and Sweep Tip #10 (Add Custom AutoCorrect with UDF) by David Hager

I was reading articles at exceloffthegrid.com and I came across this technique to add an autocorrect replacement in Excel.

https://exceloffthegrid.com/autocorrect-hack-to-speed-up-data-entry/

I thought that it would be useful to make a way to automate this process. So, I created the following VBA function procedure (put in a general module in VBE).

Function AutoCorrectAdd(ReplaceWhat As String, ReplaceWith As String)

Application.AutoCorrect.AddReplacement What:=ReplaceWhat,Replacement:=ReplaceWith

End Function

So, if cell A2 contained RobC and B2 contained https://powerpivotpro.com/, then the formula

= AutoCorrectAdd(A2,B2), run in a worksheet UDF

would add that autocorrect replacement the built-in AutoCorrect list. If you then type RobC, it would be replaced by https://powerpivotpro.com/.

You could also use this function in a Sub procedure. Say that you had an Excel list of nicknames and their corresponding e-mail addresses. You could create a Sub that operated on each row of the two column list with the AutoCorrectAdd function to convert the list to autocorrect replacements (not shown, a challenge to the reader 😊).

Hope that you find this useful.

#Excel: Using Conditional Formatting to Highlight Cells That Contain Array Formulas Using the FORMULATEXT Function by David Hager

A common story among Excel developers is the easy fixes they made for their clients to get a formula in a cell to calculate correctly. In many cases, the problem was corrected by simply entering a formula as an array formula (Control-Shift-Enter). This only takes seconds to perform, and usually the developer received a hefty amount of money relative to the task. So, if a conditional format can be applied to worksheet cells that potentially contain array formulas, the CF highlight will indicate which are array formulas.

Originally, I thought of creating an UDF with VBA that could be used as the CF Boolean formula. Then, I remembered the new Excel FORMULATEXT function and wondered whether it would return the “curly brackets” from a cell containing an array formula. I doubted that it would, but it did! 😊 So, I made a defined named formula (with E6 as the active cell) as shown in the figure below.

The formula, IsArrayFormula=LEFT(FORMULATEXT(E6),1)=”{“, is True if the first character in the string is a left curly bracket. Then, I was able to use that formula as a conditional format, as shown below.

Now, if this technique is used in auditing Excel workbooks, the cells containing array formulas will be able to be easily viewed.

The example workbook can be downloaded here.

IsArray

Excel Short & Sweet Tip #9 (Get IP Address) by David Hager

Here is a way to get the IP address of your computer. This was originally reported at

http://stackoverflow.com/questions/828496/how-to-retrieve-this-computers-ip-address

Make sure that you have the required reference (WMI Scripting) selected under Tools, References in the VBE as shown in the figure below.

Copy/paste the following code to a general module in the VBE and then type =GetIPAddress() into a worksheet cell (or, use it in a VBA procedure).

Function GetIPAddress()

Const strComputer As String = “.”   ‘ Computer name. Dot means local computer

Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i

Dim strIPAddress As String

 

‘ Connect to the WMI service

Set objWMIService = GetObject(“winmgmts:” _

& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)

 

‘ Get all TCP/IP-enabled network adapters

Set IPConfigSet = objWMIService.ExecQuery _

(“Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE”)

 

‘ Get all IP addresses associated with these adapters

For Each IPConfig In IPConfigSet

IPAddress = IPConfig.IPAddress

If Not IsNull(IPAddress) Then

strIPAddress = strIPAddress & Join(IPAddress, “, “)

End If

Next

 

GetIPAddress = strIPAddress

End Function

HTH!

#Excel: Generating a Random Sampling From a List Using VBA and the TEXTJOIN Function by David Hager

You might have a need to generate a random sampling of items from an Excel list. The technique presented here accomplishes this without any helper columns. The PickRandomFromList VBA function shown below returns a random array of items from a worksheet list, the size determined by the 2^nd argument of the function.

Function PickRandomFromList(rList As Range, sArray As Integer) As Variant

Dim N As Long

Dim Arr() As Variant

Dim lArr() As Variant

Dim Temp As Variant

Dim J As Long

Application.Volatile False

Arr = rList.Value

Randomize

ReDim lArr(LBound(Arr) To sArray

For N = 1 To sArray

J = CLng(((UBound(Arr) – N) * Rnd) + N)

Temp = Arr(N, 1)

lArr(N) = Arr(J, 1)

Arr(J, 1) = Temp

Next N

PickRandomFromList = lArr

End Function

It is important to note that this UDF does not recalculate with every change in the worksheet by using the line of code Application.Volatile False. It will only recalculate if a change is made in the cell containing the formula or in the specified worksheet range. The conversion of the array to a delimited list is done through the use of the TEXTJOIN function.

=TEXTJOIN(“,”,,PickRandomFromList(A2:A22,5))

This technique may be particularly useful for the selection of random committees from an employee list. I hope that this will give you some ideas about situations requiring random sampling.

You can download the workbook here.

PickList

Excel Short & Sweet Tip #8 (VBA Function to Return Zodiac Sign) by David Hager

Here is a VBA function procedure that will allow you to return the sign of the zodiac for a given data.

Copy/paste it into a general module in the VBE.

Public Function ZodiacSign(BirthDate As Date) As String

Dim iDayofYear As Integer

iDayofYear = DateDiff(“d”, CDate(“1/1/” & Year(BirthDate)), BirthDate) + 1

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

If iDayofYear < 20 Then

ZodiacSign = “Capricorn”

ElseIf iDayofYear < 50 Then

ZodiacSign = “Aquarius”

ElseIf iDayofYear < 81 Then

ZodiacSign = “Pisces”

ElseIf iDayofYear < 111 Then

ZodiacSign = “Aries”

ElseIf iDayofYear < 142 Then

ZodiacSign = “Taurus”

ElseIf iDayofYear < 173 Then

ZodiacSign = “Gemini”

ElseIf iDayofYear < 205 Then

ZodiacSign = “Cancer”

ElseIf iDayofYear < 236 Then

ZodiacSign = “Leo”

ElseIf iDayofYear < 267 Then

ZodiacSign = “Virgo”

ElseIf iDayofYear < 297 Then

ZodiacSign = “Libra”

ElseIf iDayofYear < 327 Then

ZodiacSign = “Scorpio”

ElseIf iDayofYear < 357 Then

ZodiacSign = “Sagittarius”

Else

ZodiacSign = “Capricorn”

End If

End Function

When entered in a worksheet cell (=ZodiacSign(A1)), where A1 contains the birthdate in question, the correct sign of the zodiac will be returned. Note that this part of the function code

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

adjusts the day of year if the year of birth is a leap year.