yogi_Conditionally Format Cells E7 To EY33 If Entry In Column E is 'Complete' and Entry In Column F Through EY is 'X'

Google Spreadsheet   Post  #2182

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-12-2017

question by Matthew Dengler:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/a0acWqzgBB0;context-place=forum/docs
Dynamically Changing Conditional Formatting in Sheets
Hi everyone,

I would like to apply a set of conditional formatting logic across a group of cells, but have the rows and cell numbers change based on the cell that I "drag/copy" the conditional formatting to.

To be clear, I would essentially like the conditional formatting logic with the following formula...

=AND(E7="some text",K7="some other text")

...To be applied to a large number of cells. But I cannot just apply the above formula to all cells, I need the rows and cell values formula to change dynamically based on each cell that I apply it to (as if I had dragged a normal formula across a group of cells). 

To be clear again, I need the above formula to change to =AND(E8="some text", K8="some other text") and =AND(E7="some text",L8="some other text") when I apply it to different rows and columns.

Can anyone help me with this?

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1lRCq05SCfZOYXe3t_fte9F6rf2LWCM77jgxkJ50ASTg/edit?usp=sharing