Excelの入力規則について

現在Excelで、少し複雑な入力規則を作成しようとしてます。
一つのファイルには一つの入力用シートと複数の管理用シートがあります。
両者は全く同じフォーマットを持ってますが、
管理用シートのセルは決まったセルにだけYが入っており、
入力用シートの全く同じ位置のセルが入力可能であることを指しています。
（入力用シートで入力不可セルの場合は、管理用シートでは空欄）

各シートには会社名（C7）と部門名（C8）の情報を持っており、
会社・部門ごとに管理用シートのYの箇所や数が異なるため、
入力用シートのC7とC8のセルの値次第で、
適切なシートのYを参照する入力規則を作成したいのです。
（各管理用シートは「管理会社名部門名」となっております、例えば管理A社B部門など）

私は入力規則でユーザー設定を選び、
空白を無視するのチェックを外した上で以下の数式を入れました：
（入力セルはE22のセルから始まってます）
=INDIRECT("管理"&CONCATENATE($C$7,$C$8)&"!"&ADDRESS(ROW(E22),COLUMN(E22),1))="Y"
この入力規則はきちんと機能しておりました。

なお、この作りには横に時間の概念が入っており（E列は2016年、F列は2017年など）
年によっては列丸ごと入力不可とするルールがあります。
私は入力用シートの16行目にYとNを入れた後（E16,F16,G16...）、
このルールを入力規則に追加するべく数式を以下のように修正しました：
=IF(E$16="Y",IF(INDIRECT("管理"&CONCATENATE($C$7,$C$8)&"!"&ADDRESS(ROW(E22),COLUMN(E22),1))="Y","Y","N"),"N")="Y"

すると「数式はエラーと判断されます。。。」のポップアップが出てきて機能しなくなります。
セルに直接上記の数式を入れた場合には正しくTRUE或いはFALSEが反映され、
また入力規則でINDIRECT~を管理A社B部門!E22など直接参照シートとセルを記載した場合にきちんと機能するため、
数式自体は間違っていないかと思います。

そのあとも色々試してみて、
もしかして入力規則の数式欄には文字制限があるのではないかと思い始めたのですが、
問題の所在が分かる方、どうかお願いします！

（わかりにくい説明になっているかもしれないので、
不明な点があれば仰っていただければ追記いたします）

質問者からの補足コメント

• 追記：
  イメージとしては例えば商品ごとの売上を記載するのが目的で、
  縦軸は商品名（A,B,C...）、横軸は年です。
  全ての会社・部門が全ての商品を取り扱っているのではなく、
  またずっと同じ商品を取り扱っているわけではないため、
  各会社・部門の管理用シートではその年に扱っている商品のセルだけにYがついています。
  
  しかし集計の目的上、管理用シートにはYが入っているにも拘らず
  特定の年のデータが全く不要になるケースがあるため、入力用シートの16行目にYとNを入れてます。
  （このY/Nは変動するため、入力規則に組み込んでおく必要があります）

    補足日時：2016/09/22 23:13

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2016/09/22 23:36

求める回答とは別の方向のアドバイスになります。

管理シート上で関数を展開してはどうか。
それなら入力規則はセルの値が「Y」であるかを判断するだけで済む。

それにどうせ新しい条件が加わるのでしょう。
ならば管理シート上で「Y」になるように数式を変更する時にも、管理シートを見るだけで変更が反映されたのかを確認できる。

この回答へのお礼

ご回答ありがとうございました。
確かに今後条件が加わる可能性もあるので、保守性を考慮したら管理シート上で数式を組み込んだ方が管理しやすいですね。

お礼日時：2016/09/26 09:24

No.3 回答者： ママチャリ 回答日時： 2016/09/24 13:53

確かにセル上で正しく動作する式でも入力規則ではNGになりますね。

どうも条件文（IFやAND)の中のINDIRECTの中のROW()、COLUMN()関数が悪さをしているようです。それらの関数を使わなければ正しく動作するようなので、こんな感じでいかがでしょうか。仕様を勘違いしていたら、ごめんなさい。

=AND(E$16="Y",INDIRECT("管理"&CONCATENATE($C$7,$C$8)&"!RC",FALSE)="Y")

この回答へのお礼

ご回答ありがとうございました。
やはり入力規則だとNGになってしまうんですね。。。

お礼日時：2016/09/26 09:28

No.2 回答者： d-q-t-p 回答日時： 2016/09/23 10:18

入力の可不可が リストを展開するまで分からない仕様というのは 扱いづらいよう

に思います。なので銀鱗さんと同意見です。

> もしかして入力規則の数式欄には文字制限があるのではないか
ひょっとして名前定義していないのですか？
入力規則のリストは Excel2007以降 名前定義しなくても使えるようにはなりま
したが 基本的には名前定義した方がいいです。

後 INDIRECT・ADDRESS関数の組合せは無意味です。 INDIRECTを R1C1参照
形式にすればいいだけなので。

INDIRECT("管理"&$C$7&$C$8&"!R"&ROW(E22)&"C"&COLUMN(E22),FALSE)

というか列は固定じゃないんですか？

この回答へのお礼

R1C1参照を失念してました。。。
確かにこの場合はそちらの方が使いやすいかもしれないですね！

お礼日時：2016/09/26 09:27