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