Excel（エクセル） 名前定義をしたリストから、indirect関数を利用して選択入力できるようにしたいのですが…

　次のようなことができず困っています。
　Sheet1とSheet2があり、Sheet2には"数字"、"英語"と名前定義された２つのリストがあります。Sheet1のセルA1には"数字"か"英語"どちらかの文字列を選択入力し、B1には、もしA1に"数字"を入力していればリスト"数字"から、もし"英語"を入力していればリスト"英語"から選択入力できるようにしたいのです。つまり、Sheet1のセルA1、B1の入力規則の種類をリストとして、元の値をそれぞれ、
　　　・Sheet1!A1には=数字,英語
　　　・Sheet1!B1には=indirect($A1)
としています。

質問(1)）　ここまでなら何の問題もないようですが、問題はSheet2の"数字"と"英語"の参照範囲です。
　　　・"数字"の参照範囲は=Sheet2!$A$1:$A$10,Sheet2!$C$1:$C$10
　　　・"英語"の参照範囲は=Sheet2!$B$1:$B$10,Sheet2!$D$1:$E$10
と複数範囲におよんでいる場合、Sheet1!B1にはうまくリストが表示されません。解決法、およびなぜ表示されないのか、どなたかご教示お願いいたします。

質問(2)）　Sheet2の"数字"と"英語"の参照範囲を
　　　・=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))
　　　・=OFFSET(Sheet2!$B$1,,,COUNTA(Sheet2!$B:$B))
とした場合も同様にSheet1!B1にはうまくリストが表示されません。こちらの方も解決法と表示されない理由をお願いいたします。

No.5 ベストアンサー 回答者： mike_g 回答日時： 2008/04/27 21:42

（シート名を指定する必要がない）次のような手法は如何でしょうか？

Sheet2
　　　A　　　 B　　　 C　　　　 D
1　 数字1　英語1　　 数字2　英語2
2　　　11　Andrew　　　 31　Siew
3　　　12　Andy　　　　 32　Steve
4　　　13　Bob　　　　　33　Stu
5　　　14　Byron　　　　34　Tibor
6　　　15　Carol　　　　35　Tim
7　　　16　Chris　　　　36　Tom
8　　　17　Clarence　　 37　Wellington
9　　　18　Dan　　　　　38　Wes
10　　 19　Dennis　　　 39　William
11　　 20　Don　　　　　40　Wing

Sheet2 において
１．範囲 A1:D100 を選択
２．[挿入]→[名前]→[作成]を実行
３．“上端行”だけにチェック入れ
４．[OK]をクリック

Sheet1 において
５．セル A1 および B1 に[入力値の種類]を“リスト”とし、かつ、[元の値]を
　　次とする[入力規則]を設定
　　A1: 数字1,数字2,英語1,英語2
　　B1: =OFFSET(INDIRECT(A1),,,COUNTA(INDIRECT(A1)))

この回答へのお礼

　お礼遅れました。
　できました。すばらしい。諦めかけてましたができるもんなんですねー。ありがとうございました。
　ただ、「２．[挿入]→[名前]→[作成]を実行」で具体的に何をしてるのかわかりませんが…。すいません、素人質問で…。これくらいは自分で調べます。
　親切に教えていただき本当にありがとうございました。

お礼日時：2008/05/03 12:52

No.6 回答者： mike_g 回答日時： 2008/05/03 19:21

[ANo.5この回答へのお礼]に対するコメント、

》 [挿入]→[名前]→[作成]を実行」で具体的に何をしてるのかわかりませんが…

[ヘルプ]の「ブック内のセルに名前を付ける」を読めばよろしいかと。
要は、セル（範囲）に名前を付けているのですよ！

No.4 回答者： mike_g 回答日時： 2008/04/27 10:17

[ANo.2この回答へのお礼]に対するコメント、

「元の値はエラーと判断されます。続けますか？」というメッセージの再現テストをしていますが、成功しません。ところで…

最初の質問中で、「Sheet1のセルA1」「の入力規則の種類をリストとして、元の値を」
「=数字,英語」「としています。」「ここまでなら何の問題もないようですが…」と書いておられますが、私の場合、問題が出ます。

[データの入力規則]メニューの[元の値]ボックス内に式 =数字,英語 と入力して[OK]をクリックした時点で「条件 データの入力規則 には、OR 演算、AND 演算、配列定数は使用できません。」というエラーが出るのです。

貴方の場合は本当に「何の問題もない」のですか？

この回答への補足

すいません。誤記です。「=」は不要でした。正確にはデータの入力規則]メニューの[元の値]ボックス内に「数字,英語」と入力しました。

補足日時：2008/04/27 17:30

No.3 回答者： mike_g 回答日時： 2008/04/26 08:56

》 質問(2)）　Sheet2の"数字"と"英語"の参照範囲を

》 　　　・=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))
》 　　　・=OFFSET(Sheet2!$B$1,,,COUNTA(Sheet2!$B:$B))
》 とした場合も同様にSheet1!B1にはうまくリストが表示されません。

何かエラーメッセージが出るならそれもキチンと示すべきかと。
「抽出条件 データの入力規則 で、他のワークシートまたはブックへの参照は使用しません」では？

この回答への補足

エラーメッセージは
「元の値はエラーと判断されます。続けますか？」
です。
「抽出条件データの入力規則で、他のワークシートまたはブックへの参照は使用しません。」
のエラーメッセージは、Sheet1のセルB1の元の値に直接
　　　=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))
とした時に現れます。

補足日時：2008/04/27 01:49

No.2 回答者： mike_g 回答日時： 2008/04/26 00:32

[入力規則]が受け入れる「リスト」は“単一列内の連続した範囲”の単一リストでなければならない、が私の理解です。

貴方は、Sheet1!B1 において（結果的に）複数のリスト（範囲）を指定しているので、期待する結果が得られないのだと思います。

この回答へのお礼

回答ありがとうございます。
どうやらそのようですね。試しに"数字"の参照範囲である
　　　Sheet2!$A$1:$A$10,Sheet2!$C$1:$C$10
の値を
　　　Sheet1!$A$2:$A$11,Sheet2!$C$2:$C$11
にコピーして、
Sheet1!のセルB1の入力規則の元の値を上記と同じ
　　　=Sheet1!$A$2:$A$11,Sheet2!$C$2:$C$11
にすると、エラーメッセージが出ました。
質問(1)に関してはそういうもんなんだということで納得しました。
どなたか質問(2)についてお分かりになられる方おられましたらお願いいたします。

お礼日時：2008/04/26 03:39

No.1 回答者： noname#79209 回答日時： 2008/04/25 23:04

実験はしていませんが、

・"英語"の参照範囲は
=Sheet2!$B$1:$B$10,Sheet2!$D$1:$D$10,Sheet2!$E$1:$E$10
と、複数列を一度に指定せず、一列づつに分解して指定したらどうでしょう。

この回答へのお礼

回答ありがとうございます。
やってみましたが駄目なんです。
"数字"の方もうまくいきませんから…。

お礼日時：2008/04/26 03:22