連動したドロップダウンリストを INDIRECT関数を利用して作成したのですが・・・

質問

お世話になります。EXCEL2003での質問です。

県選択→結果を受けて市町村選択　というドロップダウンリストを下記の要領で作成しました。
　名前の定義　：　名前；都道府県　　参照範囲；シート名!$A$2:$A$7
　名前の定義　：　名前；○○県　　　参照範囲；シート名!$B$2:$Z$2
　各県の名前を同様に定義する。

一つ目のドロップダウンリスト（別シートのA1)
　入力規則　　：　入力値の種類；リスト　　元の値；=都道府県
二つ目のドロップダウンリスト（別シートのB1)
　入力規則　　：　入力値の種類；リスト　　元の値；=INDIRECT($A$1)

上記で問題なく連動したドロップダウンリストを作成できました。が、市町村合併に対応させたくて参照範囲を可変に・・・と思い、名前の定義の参照範囲のところを次のように変更しました。

　名前の定義　：　名前；○○県　　　参照範囲；=OFFSET(シート名!$B$2,0,0,1,COUNTA($2:$2)-1)

すると二つ目のドロップダウンにリストが出てきません。
B1の入力規則に戻って元の値が=INDIRECT($A$1)になっているのを確認してOKをクリックしたところ"元の値がエラーと判断されます"と警告が出ました。
そこでINDIRECT関数を噛ませずに直接、元の値を=○○県 にしたところ正常にドロップダウンにリストが表示されました。
これは、名前の定義の参照範囲に関数を使用したときにはINDIRECT関数が使用できない、ということなのでしょうか。どなたか、お詳しい方、教えていただけませんでしょうか。よろしくお願いします。

回答No.1

noname#204879 · ベストアンサー

面白そうなので、確認させていただきたいのですが…

１．シート名!$A$2:$A$7 の範囲には各セルに東京都、北海道、大阪府、愛知県等
　　の文字列が、シート名!$B$2:$Z$2 の範囲には各セルに該当都道府県内の市町
　　村名の文字列が、それぞれ入力されていますよね？
２．「市町村合併に対応させたくて参照範囲を可変に」の意味は、市町村名のセル
　　が増減することですね？ＹＥＳなら、追加を範囲 B:Z の右に行わずに当該範
　　囲内に挿入するのでは駄目なのでしょうか？

それから、「別シートのA1」および「別シートのB1」は下方にコピーするのであれば、式 =INDIRECT($A$1) の「$A$1」は「$A1」の方が良さそうに思いましたが、如何でしょうか？

お礼

mike_g様
　おかげで将来的な変更に神経質にならずにデータを作成できます。
　また何かありましたらご教授ください。有難うございました。

補足

ご回答有難うございます。
まず説明不足で下手な文章をお詫びします。分かり難くて申し訳ありません。

　１.はお書きになられたとおりです。
　　　　　　A　　　　　 B　　　　　　 C　　　 　 D　　　 ・・・
　　1　 都道府県　　 市町村
　　2　　埼玉県　　さいたま市　　秩父市　　飯能市　　・・・
　　3　　三重県　　　津市　　　　伊賀市　　松坂市　　・・・
　　4　　山口県　　　山口市　　　光市　　　下関市　　・・・

　　上記の具合で、Aの列方向に都道府県、各都道府県の行方向にそれぞれ該当市町村を
　　入力し、必要範囲をおのおの名前定義しました。

　２．>「市町村合併に対応させたくて参照範囲を可変に」の意味は、市町村名のセルが増減する
ことですね？・・・Yesです。

　　　>追加を範囲 B:Z の右に行わずに当該範囲内に挿入するのでは駄目なのでしょうか？
　　恥ずかしいのですが、挿入する、という考えが全く頭に有りませんでした。
　　やってみたらできました。範囲最後尾に（範囲の右側に）追加する場合も、最初に範囲
　　指定する時点で１セル以上余分に指定して空白を作っておけば対応できそうです。

　　　>、「別シートのA1」および「別シートのB1」は下方にコピーするのであれば、
　　　式 =INDIRECT($A$1) の「$A$1」は「$A1」の方が良さそうに思いました
　　全くおっしゃるとおりです。

　これで当面の問題は解決しました。本当に有難うございます。
　このレスは一応、補足という形で投稿せさていただきます。
　また、改めてお礼のレスをいたします。

回答No.2

noname#262398

もう解決されたようですが…

INDIRECT関数でエラーが出る理由は分かりませんが、INDIRECTを使わずに参照範囲を縦横可変にすれば、一応出来るようです。

　名前の定義　：　名前；市町村　　　参照範囲；=OFFSET(シート名!$B$2,MATCH(別シート!$A1,都道府県,0)-1,0,1,COUNTA(OFFSET(シート名!$2:$2,MATCH(別シート!$A1,都道府県,0)-1,0)-1))

○○県という名前が入力規則のリストでしか使わなければ、不必要になります。

ただ、実用上は#1のご回答のようにして、INDIRECT関数を使った方が分かりやすいと思います。

それからご質問の件とは関係ありませんが、入力規則を2つのセルで使うと、
千葉県　さいたま市
のようなありえない組み合わせになったりするので、
1つのセルで2つのドロップダウンリストを使うのは、いかがでしょうか？

ドロップダウンリスト（別シートのA1)
入力規則　　：元の値；=IF(COUNTIF(都道府県,A1),INDIRECT(A1),都道府県)

この場合は、市町村名のリストを「○○県●●市」という形で入力しておくことになってしまいますが。

面白いので(?)、まあ、時間があったらお試しを。

お礼

ご回答有難うございます。

　縦横可変の存在は知っていたのですが、参照範囲の式を書くのが面倒だな、などと不精な
ことを思い避けていました。ですが、おっしゃるとおりにやってみたところ逆に大変便利かつ、
手間が省けることがわかりました。○○県の名前の定義を一つ一つ行うのが煩雑でしたので
助かります。
　名前定義の個数が少なくて済む場合、またはすでに定義されている名前の場合は#1のご回答の
ように挿入・削除で、これから作成するものについては積極的に縦横可変を使用してみます。

　もうひとつご指摘いただいた件ですが、
　>入力規則を2つのセルで使うと、千葉県　さいたま市　のようなありえない組み合わせに
なったりする
　・・・ことのないようにするのが今回の"肝"です。
　ドロップダウンリスト1で"千葉県"を選択した場合、
　ドロップダウンリスト2には"千葉県内の市町村"のみがリストアップされるようになります。
・・・なるはずです、データに入力ミスがなければ（これが本当の"肝"かも）。
　そもそも最初の質問での説明に不足がありすぎたと反省しております。申し訳ありません。
エクセル上の出来事をテキストだけで表現するのは難しいことですね。

　たいへん勉強になりました。また何かありましたらご教授ください。有難うございました。