別シートにある同一名義のセルをリストする方法

質問

初心者がデータ管理システムを作るハメになり、四苦八苦してます。専門用語が分からないので、お見苦しい表現があるかもしれませんが、ご容赦ください。

シート１でA1:A5を　提出日　という名前で定義します。

　　A　　　　B　　　　　
1 1月　　提出日
2 2月　　提出日
3 3月
4 4月
5 5月

この時、B1のデータ入力規則を、元の値=INDIRECT(B1)でリストするとB１には1月～5月のドロップダウンリストが作られます。分からないのはここからです。

シート２を以下のように入力します。

　　A　　
1 7月
2　 8月
3　 9月
4　 10月
5　 11月

シート２のA1:A5もやはり　提出日　として定義するとします。（このやり方は分かります）
シート１のB２において、元の値=INDIRECT(・・・・・・・)のような形でリストし、シート２のA1:A5をドロップダウンリストとして作成することはできるのでしょうか？

現状、
(1)名称の定義を　提出日　で固定しなくてはなりません。
(2)B2をリストする際の元の値を、単にシート２のA1:A5にあてるようなやり方ではダメな状況になっています。なんとしてもINDIRECTを使ってB2を変動的なセルにしたいのです。
なぜなら、私が実際に作成しているシステムは、B2が(それ以前の処理による連動の影響で)「提出日」ではなくなっている可能性もあるからです。

以上になります。
よろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

名称の定義で提出日で固定しなくてはなりませんとなっていますが、別のセルに提出日と項目を作ってその隣のセルにドロップダウンリストで月を選択できるようにしてはどうでしょう。
あなたのドロップダウンリストの設定の仕方は間違っています。B1セルに初めに提出日と入力しておきB1セルを選択してドロップダウンリストを作るために=INDIRECT(B1)と入力をしていますね。それでシート1の1月から5月がリスト表示されますが、例えばそこで1月を選択しますと、その後にはB1セルにドロップダウンでリストを表示させることができません。それは1月を選択したのちではB1セルは1月となっており名前の定義で1月が無ければリスト表示はできなくなるからです。一番最初の操作ではB1セルに提出日が入力されているから1月から5月のリストが表示されたのです。
通常のドロップダウンリストの設定の仕方は特別のことを除いてINDIRECT関数を使いませんし、空白のセルに対してリスト表示ができるように設定するのです。例えばB1セルが空白セルの状態でリスト操作の中で元の値には名前が設定されていれば　=提出日　のように入力すればよいでしょう。名前が定義されていなければ元の値には　=$A$1:$A$5　を入力すればよいでしょう。
B2の件についても同じことですがB2に提出日と入力されておりINDIRECT関数を使ってシート2のA1セルからA5セルのデータをリスト表示させるためには、元の値には　=INDIRECT("Sheet2!"&B2)　を入力させればできます。しかし、それができても7月などが選択された後ではB1セルの場合と同様にリスト表示がされなくなってしまいます。
B2セルを空白状態でもシート2のA1からA5セルがリスト表示されるようにするためにはB1の場合と同様にして　=Sheet2!提出日　と入力してもエラーが表示されて設定ができません。別のシートのデータを呼び出して使うことがそのままではできません。そこで元の値には　=INDIRECT("Sheet2!提出日") を入力すればよいでしょう。
いずれにしてもB1セルやB2セルに提出日と入力しておいてからINDIRECT関数を=INDIRECT(B1)などのように使って元の値をリストにすることは適当ではありません。ドロップダウンリストの作り方についてその基礎をよく見なおしてみることが必要でしょう。
名前の定義についても同じ提出日をシート1とシート2で作ることも必要ないでしょう。出来ればシート1では提出日1、シート2では提出日2のように定義してもよいですね。そうすることでブック全体でシートの区別がなくその名前を自由に使うことができますね。

お礼

基礎を知らない私の質問に回答していただきありがとうございました。

大変勉強になりました。お陰様で解決いたしましました。

回答No.4

keithin

失敬

シート１のＢ２に入力規則で
　種類　リスト
　元の値　=INDIRECT("Sheet2!"&B1)
または単に
　元の値　=INDIRECT("Sheet2!提出日")
とします

失礼しました。

お礼

まさにこれが解決方法となりました。

丁寧に教えてくださってどうもありがとうございました。

回答No.3

keithin

回答１で回答したとおりにすれば出来ます。
騙されたと思って，まず言われたとおりやってみてからお返事下さい。

回答No.1

keithin

＞シート１でA1:A5を　提出日　という名前で定義します。

このようにして付けた名前を「ブック単位の名前の定義」と言います。

＞シート２のA1:A5もやはり　提出日　として定義するとします。（このやり方は分かります）

このようにして付けた名前を「シート単位の名前の定義」と言います。

手順：
ご利用のエクセルのバージョンが不明ですが，名前の定義（名前の管理）で「ブック単位の名前」を全て削除します

改めてシート１のA1:A5に
Sheet1!提出日
という名前で，シート単位の名前を定義します

シート２のA1:A5には
Sheet2!提出日
という名前で，やはりシート単位の名前を定義します

重要なのは，ブック単位の「提出日」を『作らないで』，全てシート単位の名前で「提出日」を用意するということです。

あとは
シート１のＢ１に提出日と記入し，
Ｂ２の入力規則で
　種類　リスト
　元の値　=INDIRECT(B1)
と設定します

シート２のＢ１に提出日と記入し，
シート２のＢ２の入力規則で
　種類　リスト
　元の値　=INDIRECT(B1)
と設定します。

補足

ご丁寧にありがとうございます。
大変勉強になりました。

すみません。私の言葉足らずで、keithinさんには「Sheet2!B2の方にリスト付けするにはどうすればいいか」の回答をしていただいてるのですが、正しくは「Sheet1!B2のリスト付けを=INDIRECTでするにはどうすればいいのか」が質問内容だったのです。

改訂した質問文書を一応載せておきます。
ご存知でしたらご教授ください。

Sheet１を以下のように入力し、A1:A5を 提出日 という名前で定義します。

　　A　　　B
1　１月　提出日
2　２月　提出日
3　３月
4　４月
5　５月

この時、B1のデータ入力規則を、元の値=INDIRECT(B1)でリストするとB１にはSheet1!A1:A5すなわち1月～5月のドロップダウンリストが作られます。分からないのはここからです。

Sheet２を以下のように入力します。

　　A
1　7月
2　8月
3　9月
4　10月
5　11月

Sheet2!A1:A5もやはり 提出日 として定義するとして（このやり方は分かります）、Sheet1!B2を、元の値を=INDIRECT(B2)としてリストすると、B2にはSheet1!A1:A5の1～５月がドロップダウンリストで出てきてしまうのです。私はこのSheet1!B2をSheet2!A1:A5すなわち７月～１１月のドロップダウンリストで作成したいのですが、それができずに困っています。

なお、Sheet1!B2にリストする際の元の値に、Sheet2!A1:A5を直接あてるようなやり方ではダメな状況になっています。なんとしてもINDIRECTや関数を使ってSheet1!B2を変動的なセルにしたいのです。なぜなら、私が実際に作成しているシステムは、Sheet1!B2が(それ以前の処理による連動の影響で)「提出日」ではなくなっている可能性もあり、その場合にSheet1!B2にSheet2!A1:A5がドロップダウンリストで出てくるとおかしくなってしまうからです。

まとめますと

Sheet1!A1:A5の名前定義を提出日にしている前提で、Sheet1!B2に、Sheet2!A1:A5(こちらの名前定義も提出日)をドロップダウンリストとして、元の値として直接あてるのではなく=INDIRECTで作成するにはどうすればいいのか？

が質問となります。

以上になります。
分かりにくかったらすぐに補足します。
よろしくお願いします。