Category Archives: Excel

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

 

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Is3ddn1

Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames

#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.

=SUM(Sheet1:Sheet2!B2:B5)

So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:

=SUM(C2:C5,Sheet2!C2:C5)

So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.

=MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:

=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:

=IFERROR(MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)<=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0),FALSE)

Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.

Is3D1

You can download the example file here.

IsNative3DFormula

#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

Conditional fomatting (CF) in Excel is a powerful tool for highlighting cells that meet certain criteria based on a formula. However, to my knowledge a worksheet formula has never been used with CF to highlight cells containing user-defined functions (UDF). Presented here is a method to accomplish this.

In order to lookup whether a formula is a UDF, a lookup table of all Excel worksheet functions is required. I found the list for this at this Microsoft site.

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

The list is in the range A2:A472 in the example workbook. It was defined as xlFunctionList. I tried to just name an array of function names, but it exceeded the number of characters allowed.

So, with E2 as the active cell, I created the following defined name formula:

IsUDF=ISERROR(MATCH(MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2),xlFunctionList,0))

The formula =MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2) locates the first occurrence of the left parens. This value in used by the MID function to return a string with the function name. Then, the MATCH function looks up whether that string is in the function list. If it is not there the ISERROR function will return True. Thus, when applied as conditional formatting to cells E2:F2, E2 has a yellow highlight, indicating that a UDF function is in that cell. F2, containing a native function, is not highlighted. Note, though, that this technique only works if the function is at the beginning of the formula. Perhaps a followup to this article will deal with that issue.

isudf1

You can download the example file here:

IsUDF

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

I wanted to improve a few design features and eliminate a few bugs in some of my related recent posts,

so I have combined the concepts demonstrated by the following 4 articles located at:

https://dhexcel1.wordpress.com/2017/03/30/excel-filtered-list-using-a-calculated-column-to-count-occurrence-number-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-creating-a-list-made-up-of-formulas-from-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

into a single workbook. Among the changes I did/did not make are:

  1. No change to the formula that given occurance number for records in a filtered list.
  2. I moved the formula list from above the filtered list to another worksheet.
  3. I fixed some issues with the VBA code for the Filter Criteria UDF. I then located it with the
  4. the static list on the other worksheet.
  5. I included the Advanced Filter search with a custom list with this model.

In moving the static calculated list to another worksheet, I had significant problems adjusting the formulas to work in their new location. For the formula:
=INDIRECT(“Sheet1!a”&SMALL(IF(SUBTOTAL(3,OFFSET(Sheet1!$C$24:$C5023,ROW($C$24:$C5023)-MIN(ROW($C$24:$C5023)),,1)),ROW(1:5000),””),ROW()-2)+26-ROW())

I had to add the sheets names in the parts of the formula that point to the filtered list on Sheet1. Also, to create the correct positioning of the first formula(s) in the list (place on row 3 instead of row 2), I had to change:

ROW()-1)+25-ROW())

to

ROW()-2)+26-ROW())

The FilterCriteriaEnh function was amended to fix several faults – an error handler was added to fix the scenario where .Criteria2 did not exist and adding a line of code (Criteria2=.Criteria2) in case it did (see code in the example workbook).

The criteria UDF was relocated to the row above the static list. The following formula in A1 on the Static worksheet is:

=IFERROR(FilterCriteriaEnh(Sheet1!A23:A5023),””)

Note that the range in the UDF argument points at the filtered list on Sheet1.

aaRefine1

The list used for the advanced filter lookup is also located on the Static worksheet. As expected, when I activated the advanced filter the filter criteria UDF did not return a result, since no “filter” was applied to the list.

I hope that I have explained what is available with this new filter model, but if not, please go back and reread the 4 base articles.

You can download the (enhanced) file here.

FilteredResultsTable_AdvancedFilter

 

#Excel: Using Advanced Filter with a Custom List By David Hager

In a previous article about Excel’s advanced filter,

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

I used a reference to Charley Kyd’s use of SUMPRODUCT to filter a list based on a custom list.

http://www.exceluser.com/formulas/search-list-with-sumproduct.htm

In his article, he created a formula used in a helper column to filter based on a custom list of criterias.

I realized that the same type of formula could be used in Excel’s advanced filter. A lot of the utility of using the Excel advanced filter feature is hindered by the fact that an array formula cannot be used as a criteria. However, since the SUMPRODUCT function creates a non-array formula, I surmized that it could be used as a criteria.

BTW, Rob Collie and I published an article on this same subject in PowerPivot a few years ago.

https://powerpivotpro.com/2011/04/calculating-a-sum-based-on-a-list-criteria/

In this scenario, a list containing a column of employee numbers needs to be filtered by a custom subset of those numbers. This could be done by manually selecting those numbers from the filter dropdown criteria, which would take a very long time for a large custom list. The custom list resides on a different worksheet than the list to be filtered (in this case, on the List worksheet defined as EmployeeList).

The criteria formula (in B2) is:

=SUMPRODUCT(NOT(ISERROR(SEARCH(EmployeeList,A5)))+0)>0

Note that this formula “starts” at A5, the first item in the column.

Before applying the filter, the column looks like this:

 MultiAdv1

The advanced filter information is set up as shown below.

 MultiAdv2

Finally, the list after filtering looks like this:

 MultiAdv3

In this example, there is only one column in the list/table. Obviously an employee table would contain many more columns. Also, the employee custom list could potentially could contain 1000’s of employee numbers. I hope that you use this technique useful in your work on employee records.

You can download the example file here.

MultAdvFilter

 

#Excel VBA: Create a Table of File Locations and URLs for Your Favorites by David Hager

This is one of my personal Excel applications that I have used over the years to collect information on my Internet Favorites. When you run the CreateURLListFromFiles procedure, it creates a list of URLs and favorites file location. You browse to your Favorites folder and select to generate the table. Column B contains the URLs that can be converted to hyperlinks by selecting them and running the Convert To Hyperlinks procedure. You can use this on multiple folders where your favorites are stored and the results will be appended. Enjoy!

You can download the file here.

MyURLCollector

#Excel Identifying if a String is the Anagram of Another String Using the TEXTJOIN Function by David Hager

Using Excel native formulas to identify anagrams is quite rare. I could find only one example.

https://excelxor.com/2014/08/30/advanced-formula-challenge-2-results-and-discussion/

The methodology for comparing strings as anagrams starts with these formulas, which use B3 and C3 as the locations of the strings.

CodeString1=CODE(MID(UPPER(Sheet1!$B$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$B$3))),1))

CodeString2=CODE(MID(UPPER(Sheet1!$C$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$C$3))),1))

Each of these formulas returns an array of numbers corresponding to the character code of the letters in the string (converted to all UPPER). Why make an array of numbers? So that they can be sorted. So, the next formulas:

CodeStr1Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(B3))))

CodeStr2Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(C3))))

create an array of numbers that are sorted from smallest to largest. So, if both strings contain the same number and frequency of letters, the arrays will be identical. The TEXTJOIN function is used here to create strings from those arrays for comparison.

NumberStr1=TEXTJOIN(“”,,CodeStr1Sorted)

NumberStr2=TEXTJOIN(“”,,CodeStr2Sorted)

The formula indicate that the string in C3 is an anagram of the string in B3 is shown below.

=EXACT(NumberStr1,NumberStr2)

Note that this solution unfortunately does not include an Excel native formula or grouping of formulas to determine whether a string is an actual word (not possible). So, although it is not required, a VBA worksheet function is included here as part of the solution. You can look at the code here. WordPress.com will not allow me to publish an .xlsm file. So, please add a module in the VBE (Alt-F11) and copy/paste this function procedure into the module. When you save it, you will have to save it as an .xlsm file.

Public Function IsWord(ByRef Text As String) As Boolean

Dim wd As Object

Application.Volatile True

On Error Resume Next

Set wd = GetObject(, “Word.Application”)

If Err.Number <> 0 Then

Set wd = CreateObject(“Word.Application”)

If Err.Number <> 0 Then

Set wd = GetObject(, “Word.Application”)

End If

End If

IsWord = wd.CheckSpelling(Text)

Set wd = Nothing

End Function

 

Here is the resulting formula to determine whether both strings are words and anagrams.

=AND(IsWord(B3),IsWord(C3),EXACT(NumberStr1,NumberStr2))

Remember, you need the correct version of Excel 2016 (Office 365) in order to use the TEXTJOIN function.

You can download the file here.

Textjoin_Anagram