Forum Discussion
Conditional Format calculated across relative references, applied to an absolute reference
Hi there 😊 I would like to place some conditional formatting on some helper text to highlight it when a user has made an error. I have created a dummy file to replicate what I am trying to do - a combination of "Bargain" selected in column D / [Price] and "High" in column F / [Demand] should trigger the rule and format cell H2. I've tried a bunch of different approaches but have only been successful in getting the formatting to respond to combinations in a single row rather than all rows. I'm sure that this will get solved quickly but it's been doing my head in! Looking forward to your advice - thanks in advance 🤗
UPDATE: I've at least been able to get a formula to output "TRUE" to a single cell... but I can't get it to work as a conditional formatting formula and would like to avoid using a helper cell if I can. The following formula works if I place it in a helper cell that I specify the conditional formula to check: =IF(FILTER(Table1[Type],(Table1[Price]="Bargain")*(Table1[Demand]="High"),"")<>"","TRUE")
SergeiBaklan That is brilliant, thank you. I only learned of the "*" operator in the past few months when my employer finally upgraded to Office 365 - but I learned it in conjunction with the FILTER function as part of the syntax I needed at the time (and have used extensively since). I had no idea it could be used outside of FILTER until now 😳 It will come in handy for me with type of work I tend to do in Excel.
