スプレッドシートの条件付き書式（カスタム数式）を参照元をずらして反映させたい

Question

知りたいこと

特定の範囲に設定した条件付き書式（カスタム数式）をそのまま別の行に反映させることは可能でしょうか。
（数式内に相対参照あり）
方法があればご教示ください。
そもそも不可能な内容の質問であればご容赦ください。

実現したいこと

添付画像のように、B2:F6の範囲で2行目が「完了」であれば、完了が入っている同一列の2行目〜6行目に色を付ける、という条件付き書式をそのままスライドさせて、B7:F11の範囲に7行目が完了だったら7行目〜11行目に色を付けるというように反映させたい。

試したこと

B7:F11の範囲にindirectを用いて行数を可変できるよう「countif(indirect("B$"&row(B2)+5),"完了")」と入れてみましたが、条件付き書式のカスタム数式では「$」が生きないのか、「countif(B$7,"完了")」といれた時のような動きにはなりませんでした。

Answer 1

条件付き書式は、書式を適用するセルを起点としてロジックを組む必要があります。
なので、

引用テキストB2:F6の範囲で2行目が「完了」であれば、完了が入っている同一列の2行目〜6行目に色を付ける

という視点を書式を適用するセルからみたロジックに変換してあげて、

自分（対象セル）を含む上方向 6セル内に"完了"がある場合に色を付ける

という条件でカスタム数式を作ればよいです。そうするとOFFSETでいけそうだなってなるかと。

ただ、OFFSETで 行方向に -5する場合、当然ですが5行目以下の上の方のセルに対してはエラーになるので、そこを切り分ける必要があります。

範囲を B2:F
カスタム数式を
=IF(ROW(B2)<6,COUNTIF(B$2:B2,"完了")>0,COUNTIF(OFFSET(B2,-5,0,6,1),"完了")>0)

Comments

[UY4_kxOmC]

ありがとうございます！
イメージ通りの結果になりました。offset関数が苦手なのでとても勉強になりました。

Answer 2

範囲を B2:F11 とするとき
カスタム数式
if(Row()<=6,B$2="完了",B$7="完了")　
です

Answer 3

列に対して設定するのであれば式は
B$2="完了"になるように思いますが。

Comments

[UY4_kxOmC]

ご助言ありがとうございます。

そのパターンでカスタム数式を入れた場合でも最初の2行目〜6行目までは反映しますが、主旨であるその条件を今度は7行目〜11行目に反映させることはできないようでした。

[usekay]

offset(D3,-OFFSET(D3,0,if(COLUMN()<4,column($A3)-COLUMN(D3),Column($D2)-column())),if(4>COLUMN(),COLUMN($B1)-COLUMN(),COLUMN($D1)-COLUMN()))="完了"
とすれば表の一番左の列に項目番号が入っている限りは確実に判定できますし表の縦幅がバラバラでも変わっても適切に処理できますね。
3~6行目に完了と入ってもこの場合誤検出されません。(はいるかどうかはわかりませんが)
オフセットの基準列を変えるだけで右の方にも適用できますし。

[usekay]

まあ結局はどこが変わってどこが変わらないか次第ですかね。

[UY4_kxOmC]

追記ありがとうございます。
普段使わない数式で慣れていないためか、うまく反映できませんでしたが、こちらも参考にしてうまく反映できるように勉強して参りたいと思います！