Google Sheets conditional formatting based on cell value 3 rows above in same column

Question

Within Google sheets, I'm trying to write a custom formula within conditional formatting to turn a cell value the color 'pink' when a cell value (that is 3 rows above) is labeled 'Yes'.

Here is an example of what I'm trying to achieve. Hopefully one of you can help me work out the custom formula!

https://docs.google.com/spreadsheets/d/1GvWgFbE9QQL6STm3dYLMInp6QVw_tN_brlqKzwnwglI/edit?usp=sharing

Answer 1

The sheet you linked in your question is not public.

Based on what you wrote, try this in "Conditional format rules" >> "Custom formula is"

=indirect(address(row()-3,column()))="Yes"

Answer 2

all you need is offset the range by 3 rows:

=D4="Yes"