Intro From Rob: Greetings from Vegas!
Well ok, Vegas isn’t tons of fun when you don’t leave your hotel room very much, but hey, I have a great view of castles and downtown Manhattan. I’m a little worried that this photo may open a wormhole into some alternate universe however:
Hey Wait a Second – How Is this Picture ALREADY in
the Blog Post Being Written in The Same Picture??? My Head Hurts.
Anyway, today David Hager has graciously stepped to the plate with a guest post. He’s seen me goofing around with conditional formatting a lot and he’s got some advanced techniques to add to the mix. Today’s post sets the stage for that.
It all revolves around the capability of CF (Conditional Formatting) to use formulas as the “decider” of whether or not to format a cell:
This CF Rule Type is Important to David’s Techniques
All right, take it away Mr. Hager…
CHANGING CONDITIONAL FORMATTING LOGIC USING DROPDOWNS
By David Hager
There has been a lot of demos and discussion on this blog relating to the innovative use of conditional formatting (CF), particularly in connection with PowerPivot.
https://p3adaptive.com/?s=%22conditional+formatting%22
The technique of CF in Excel is especially powerful where formulas are used to define the desired TRUE/FALSE condition to be applied. It occurred to me that in this era of data visualization and controls (such as slicers) for altering the data view, there was no user method for changing the underlying logic of applied conditional formats. As an example, say that on a worksheet that cell D7 has a CF formula of =D7>8. There is no way to change the CF to =L7>8 unless the CF control on the Ribbon is opened and the formula is manually changed. Further, a CF formula with both conditions ( =AND(D7>8,L7>8) ) may be what is desired, and the user may not know how to do this. Thus, I decided to create a method that allows for the selection and changing of CF conditions from dropdowns using data validation lists.
To make a long story short, I had to find a way to tie a text value in a cell to the same value as a defined named formula Boolean expression. The way I accomplished this was by using the very powerful but seldom-used CHOOSE function. For those BI-centric readers, this function is similar to the SWITCH function in DAX. In spite of my familiarity with the use of the CHOOSE function in building complex solutions (see https://spreadsheetpage.com/index.php/eee/issue_no_2_april_1_1999/), I did not know that it could return a Boolean expression. The defined name formula I came up with is shown below:
DropdownCondition1=CHOOSE(MATCH(Dropdown1,ConditionList,0),Condition1,Condition2,Condition3,Condition4,Condition5,Condition6,Condition7,Condition8,Condition9,Condition10)
Dropdown1 is a named cell containing a data validation list populated by the named range ConditionList, which is a 10 cell range (H2:H11 in this example) containing text describing each condition. The MATCH function returns a number from 1 to 10 based on the selection in Dropdown1. That number is used by the CHOOSE function to return the matching condition. All of the naming presented here is generic – more descriptive names would be appropriate for the Boolean expressions in a real model. The conditions used in this example are commonly used to find text in strings, so Condition1 is:
=NOT(ISERROR(FIND(Sheet1!$J$2,Sheet1!A1)))
Condition2 ..$J3..etc. as shown in Fig.1.
For those that are unfamiliar with creating defined name formulas, it should be noted that if relative references are in the formula, the active cell must be the same location when defined (in this example, A1 is the active cell).
The cells containing the data validation are B1:B5, as shown in Fig.1. When the selection is made in any of these cells, the corresponding Boolean condition is activated in the CF formulas shown below.
AndCondition=AND(DropdownCondition1,DropdownCondition2,DropdownCondition3,DropdownCondition4,DropdownCondition5)
OrCondition=OR(DropdownCondition1,DropdownCondition2,DropdownCondition3,DropdownCondition4,DropdownCondition5)
Figure 1
In the example, there are 3 conditions selected, since Dropdown4 & 5 are the same as Dropdown1. So, AndCondition becomes equivalent to: =AND(Condition1, Condition2, Condition3) (same for OR).
The CFs are applied in column D & E.
The AndCondition and OrCondition formulas are only examples of what can be used, as are the CF conditions. It is the methodology that is important, and the flexibility and power it provides to a specific dashboard presentation.
Now, you might ask, what does this have to do with PowerPivot? Well, it turns out that CUBE functions can be used in defined name formulas. Thus, boolean expressions with formulas containing CUBE functions can be used in the same manner as described here. And, since these formulas as derived from converted PowerPivot pivot tables respond to changes in slicer selections, powerful possibilities exist for the control of CF on PowerPivot dashboards. This will be demonstrated in an upcoming article. In the meantime, enjoy using and modifying this technique for your own visualization solutions (see workbook link).
One last note from Rob
Data validation dropdowns don’t work on the server, so if you’re publishing to the web, you may want to consider using slicers instead of data validation dropdowns, and then a technique like this one to get the slicer’s “result” into a cell. From that point forward, I haven’t thought about how much that changes his technique – I’m tempted to say that it doesn’t change it at all, but I haven’t thought about it enough and am probably wrong.
