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

Question

##知りたいこと
Googleスプレッドシートを使っています。

条件付き書式設定で
別シートの固定セルの値を呼び出すがわかりません。

##具体的には

シート1のA1セルの値が仮に1000と入力されているとして、
シート2のB列に数字が入力されているときに、
シート2のB列の数字が、シート1のA1セルの値以上だったときだけ
シート2のB列の対象セルを強調表示させたいと思っています。

##試してみたこと

シート2のB列へ条件付き書式ルールで
条件を「以上」「'シート1'!$A$1」としましたが、
適用されませんでした。

次に、
条件を「以上」「='シート1'!$A$1」とすると
「計算式が無効です」とエラーがでて登録できませんでした。

次に、
条件を「カスタム数式」「=$B1>='シート1'!$A$1」としましたが、
「計算式が無効です」とエラーがでて登録できませんでした。

よろしくお願いいたします。

Answer 1

スプレッドシートの条件付き書式は、なぜか別シート参照の際は INDIRECTを経由する必要があります。
このケースだと 条件を「以上」とする場合に

=INDIRECT("'シート1'!$A$1")

と指定してあげる必要があります。参照先を可変（相対参照）にしたい場合は、これにROWやCOLUMN、Cellといった関数を組み合わせてあげる形になります。

参考
https://qiita.com/dorarep/items/5709035d79745c2ed8ad

Comments

[kikuchi33]

ご回答ありがとうございます。

早速お教えいただいた通り、
条件「以上」でINDIRECTを指定しましたが、
計算式が無効とエラーが出て登録ができませんでした。

どのような原因が考えられますでしょうか？

お忙しいところ恐れ入りますが、
引き続きお知恵をお貸しいただければ幸いです。

よろしくお願いいたします。

[sawa]

すいません。INDIRECT内の ダブルクォーテーションが漏れてました。修正しましたのでお試しください。

[kikuchi33]

ありがとうございます。

試してみたところ、
別シートのセルを参照できました！

いつも貴重なお知恵をありがとうございます。

Answer 2

結論: 別のシートのセルを条件付き書式で設定することはできない。

検証:
0. シート2で条件を「以上」「='シート2'!$A$1」として条件付き書式を設定→設定可能で動作する
0. 条件を編集し、「2」を削除する→「条件付き書式ルールで別のシートを参照することはできません」というメッセージボックスが表示され、変更できない

対策:
条件付き書式を設定したいシート2のどこかのセルに、数式「='シート1'!$A$1」を設定し(表示させたくなければ非表示にし)、条件付き書式ではそのセル番地を指定する。

Comments

[sawa]

スプレッドシートの条件付き書式はわかりずらいのですが、一応できるのです。

[Daregada]

なるほどー、ひとつ賢くなった。

「INDIRECTを使えばできます」と表示せずに、「別のシートを参照することはできません」とわざわざメッセージを用意しているあたり、Googleとしてはやってほしくないことなのかもしれませんね。

[kikuchi33]

ご回答ありがとうございます。

条件付き書式を設定したいシート2に
シート1のA1の値を転写するのは確かにシンプルに解決しますね。

ありがとうございます。