条件付き書式で別タブのシートに色を反映させる

条件付き書式って同じシート内でしか使えないのかと思っていたけど、カスタム関数にindirectを使うと別シートの値変更も使えるらしい。知らなかった。

スプレッドシートの条件付き書式で別シートの値を参照したい

別タブの同じ会社名の行を色付けしたい

今回は次のような要件で相談を受けた。
シートAで特定のステータスに変更されたとき、シートBにある同じ会社名の行も条件付き書式で色付けしたい。

先に上げたINDIRECT関数で別シートを指定する方法だと、単にシートAと同じ行数の色が変わるだけで、「同じ会社名、かつ、特定のステータスになった」という要件が達成できない。シートB側だとステータスも会社名も一致していない謎の行の色だけ変わる感じになってしまいます。

特定のステータスになった会社名と同じ会社名の行を特定して色を変えないといけないからGAS使わないと無理かなーと思ったんですけど、なんかいけました。

処理用の列を1列だけ追加すれば解決

なんてことないっすね。
条件付き書式で対応できない理由は2列分の値を突合しないといけないことが原因で、なんとか1つの値として取り扱えないか考えた末に、シンプルに別シートから参照する用の列を1列追加すれば解決するんじゃないかと思った。

こんな感じ。

I列を新たに作成

別シートから参照させるときはI列を参照させればOKということになります。

条件付き書式を設定

別タブにあるシートを開き、条件付き書式を設定します。
範囲を選択し、書式ルールをカスタム数式に設定。以下の関数を設定します。

=countif( indirect( "シートA!I:I" ), $B1 & "済" ) > 0

COUNTIF関数で条件に一致する要素が1つ以上あれば色付けするという関数です。条件が何かというと、シートAのI列の値が"会社名(例の場合B1) + 特定のステータス"と一致するときという条件になります。

で、結果を見てみるとこんな感じになります。
ステータスの一致する行だけでなく、ちゃんと会社名も一致する行だけが色付けされるようになりました。

もっと良いやり方あるのかもしれないけど、一旦閃いた方法を書き留め！！