CODEWITHSUNDEEP
google-sheetsconditional-formatting
Klein
Klein

Reputation: 11

Conditional formatting in google sheets with relative cell referencing

I have a Google sheet where I need to dynamically update cell background color in multiple cells within a table based on the compared value of another cell in a table within the same sheet(Cell value in the reference table gets updated through a form which updates value regularly(Yes,No or NA values in ref table)

The table being referenced is in the range B989:W1008. And matched data in the other table; to be color coded based on string match in the reference table (Green if Yes, Red if No, empty or NA is white); is present at C2:X21.

The match is made on this condition: If B989 cell value is Yes, color code for background of cell C2 is green, No yields red, NA or Empty yields white background.

I have the conditional formula for this as =B989="Yes" for green and =B989="No" for red but need to extend this to refer each cell individually to the next cell across the reference range. ie C2 gets referred to B989, C3 to B990, D2 to C989 and so on. I'd like to avoid manually editing in the formula for individual cells

What can I use for this?

Upvotes: 1

Views: 530

Answers (1)

Isikyus
Isikyus

Reputation: 73

Can you use INDIRECT to handle a cell offset, similar to what's described in Google Sheets conditional formatting based on relative reference using another sheet's cell values?

Something like this:

=INDIRECT("R"&ROW()+987&"C"&COLUMN()+1,FALSE)

This is using the optional is_a1_notation parameter described in https://support.google.com/docs/answer/3093377?hl=en&ref_topic=3105472

Upvotes: 0

Related Questions