Tag Archives: conditional formatting

#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 3rd 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 3rd 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 1st argument. The undocumented feature of the 3rd 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.

RowCrit1

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

Advertisements

#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

 

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

IsArray2

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.

IsArray1

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: 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

Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager

 

Quite a while back I created a formula to count the number of unique items in a filtered list. For examples, see:

http://blog.contextures.com/archives/2010/10/04/count-unique-items-in-excel-filtered-list/

and

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/

I decided to extend this methodology to the conditional formatting of a filtered list. The following defined name formulas are required.

Rge=$A$5:$A$29

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

cfUnRge=INDEX((N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1)),ROW()-4)

(The cursor must be on A5 when the cf function is defined and applied to A5:A29)

So, before filtering, the list shows the 1st unique items highlighted in yellow.

 unFig1

After filtering (removing the letters b,e,f,g), the resulting filtered list looks like this.

unFig2 

I hope that this is another useful tool to add to your Excel bag of tricks. You can download the file from the link below. Enjoy!

unFiltered

Chapter on Conditional Formatting in Excel by David Hager (a Blast from the Past)

I wrote this conditional formatting article almost 20 years ago, with the hope that it would  be included in a book. Unfortunately, it never made it. I then published it as part of my Excel E-Experts series. The text files were recently added to this blog.

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

This article was perhaps one of my best works in Excel. So, although it was written long ago, it still contains valuable information that I am sharing here. The following file links are the working Excel file and the article document. Enjoy!

CONDFORMAT1

CFarticle