入力規則のリストの連動について

いつもお世話になります。
エクセル2010　WIN7 です

エクセル２００３の時に作成したリストの連動の数式をエクセル２０１０に適用すると下記のようなエラーメッセージが出ます。

　「条件データーの入力規則には、参照演算子(OR 演算子、AND 演算子、範囲演算子など)および配列定数は使用できません。」

「データー」　「データーの入力規則」　「元の値」の所には次のような数式を入れています。
主
=INDIRECT("Sheet2!A1:AE1")
サブ
=INDEX(INDIRECT("Sheet2!A1:AE15"),2,MATCH(A2,INDIRECT("Sheet2!A1:AE1"),0)):INDEX(INDIRECT("Sheet2!A1:AE15"),10,MATCH(A2,INDIRECT("Sheet2!A1:AE1"),0))

ご指導を仰ぎたいのは
多分ですがサブの数式がよくないと思われます。
このどう変えればいいのか、または他の方法にしなくてはいけないのかをご指導いただけないでしょうか。
宜しくお願いします。

No.3 ベストアンサー 回答者： kagakusuki 回答日時： 2012/11/17 17:11

　Excel2010だけで有効な方法とするのでしたら、サブを以下の様にされては如何でしょうか？

=OFFSET(Sheet2!$A$2:$A$10,,MATCH(A2,Sheet2!$A$1:$AE$1,0)-1)


　又、Excel2003以前でも有効な方法とするのでしたら、サブを以下の様にされては如何でしょうか？

=INDIRECT("Sheet2!R2:R10C"&MATCH(A2,INDIRECT("Sheet2!A1:AE1"),0),FALSE)


　尚、私の所にはExcel2010はありますが、Excel2003は御座いませんので、上記2つの数式がExcel2010で使える事は確認しておりますが、2番目に挙げた式がExcel2003以前で有効であるか否かは確認できておりません。
　ですから、万が一、2番目に挙げた式がExcel2003以前で有効ではなかった場合には、次の式もお試し下さい。

=INDIRECT("Sheet2!R2C"&MATCH(A2,INDIRECT("Sheet2!A1:AE1"),0)&":R10C"&MATCH(A2,INDIRECT("Sheet2!A1:AE1"),0),FALSE)

この回答へのお礼

いくつかのご提案をいただきありがとうございました。
どの式も確認をさせていただきました。
今後ともよろしくお願いします。

お礼日時：2012/11/18 15:30

No.2 回答者： KURUMITO 回答日時： 2012/11/17 16:00

主となっているリストの元の値はそれで良いのですがサブとなっている式はINDEX関数を使う、MATCH関数を使うといった式では対応できません。

例えばシート１のA２セルから下方には主が設定され、例えばB２セルから下方のセルではA列の値に応じた値のリストを表示させるという操作でしょう。
例えばシート２のA１セルから右横方向には　あ、い、う、え、お、か・・・・と並んでいるとします。
そこでA２セルからA１０セルまでを範囲として選び「数式」タブの「名前の定義」から「名前の定義」を選択し、表示の画面で名前の窓にA１セルと同じ値の　あ　を入力します。同様にB２セルからB１０セルを選択して　い　と名前を定義します。すべての範囲でそのように名前を定義します。
その後にシート１では２行目からデータを入力するとしてB２セルからB１０セルを範囲として選択したのちに「データ」タブの「データの入力規則」から「データの入力規則」を選択します。表示の画面で入力値の種類で「リスト」を選択し、元の値の窓には　=INDIRECT(A2) の式を入力してOKします。
肝心なことは名前の定義を使って対応することですね。

この回答へのお礼

ご丁寧な説明をしていただきありがとうございました。

お礼日時：2012/11/18 15:33

No.1 回答者： MarcoRossiItaly 回答日時： 2012/11/17 14:18

お示しになった「サブ」の数式は、Sheet2!A1:AE15 のセル範囲に存在する値が A2 セルに入力されていれば、リストでも正しく機能するのではないかと思います。

当然ながら、完全に一致しなければなりません。全角／半角の不一致、余計なスペース文字の混入などがあると、失敗します。

また、リストではなくワークシートのセルに「サブ」の数式を入力しても、正しく 9 つの値を参照します。C1:C9 だとか、どこかの 9 行 1 列の範囲を選択した状態でその数式を入力し、Ctrl+Shift+Enter のキー操作により確定すれば、できます。

ところで「主」については、リストでも INDIRECT 関数は使わなくても大丈夫と思います。

主　=sheet2!a1:ae1

ところが「サブ」については、お示しのように、なぜか INDIRECT を 4 か所とも付けないと、エラーになる仕様のようです。複雑な数式になったから？

この回答へのお礼

早速にご回答をいただきありがとうございました。

お礼日時：2012/11/18 15:36