【Excel】プルダウンリストで選択した内容に応じて参照するリストを変える方法

はじめまして、ご覧いただきありがとうございます。

現在、ExcelのシートAにプルダウンリストを置き、シートBに記載したリストを参照しています。
シートAには「会社」というプルダウンがあり、そこで選択された会社に応じて「部」というプルダウンリストに表示する内容を変えたいです。

調べたところ、シートBの「会社別の部リスト」のセルに名前を定義し、INDIRECT関数を使用すれば切り替え可能とのことだったのですが、名前の定義に半角カナや（）が指定出来ず、シートAで選択させる会社名に半角カナ、（）が含まれるため困っています。
※〇〇ｻｰﾋﾞｽ（株）　　←のようなイメージです。

会社プルダウンで選択する文字を、VBAマクロ内で別のデータと比較している為、出来る限り会社名は変えたくないのですが、関数などで部プルダウンに表示する内容を切り替える術はないでしょうか。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2018/12/03 16:13

こんにちは

考え方のみの回答です（テストはしていません）

＞名前の定義に半角カナや（）が指定出来ず、シートAで選択させる会社名に
＞半角カナ、（）が含まれるため困っています。
名前の定義には、全角の略称（略称でなくても、何でも良いですが）を用いることにしておいて、プルダウンの値からその略称に読み替えて参照するようにすればよさそうに思います。

具体的には、「プルダウンの会社名」と「名前の定義に用いた略称」の対照表を別に作っておいて、Vlookupなどで参照すれば良いのでは？
「部」のプルダウンの元リストの参照先は以下の様な形式になるものと想像します。
　INDIRECT( VLOOKUP(A1,SheetB!A1:B20,2,0) )
（式のイメージのみですので、セル位置等は適当です）

この回答へのお礼

ご回答頂きありがとうございます！
名前の定義とプルダウンの名称を置き換える発想はありませんでしたので大変勉強になりました。
こちらの方法で実装出来ました。ありがとうございます。

お礼日時：2018/12/05 11:15

No.3 回答者： 銀鱗 回答日時： 2018/12/03 17:58

んじゃ、

別のシートにその表に対応する範囲を記述しておけば良いんじゃないかな。

　〇〇ｻｰﾋﾞｽ（株）に対して ”Sheet2!A1:AX300”
とか
　〇〇ｻｰﾋﾞｽ（株）に対して ”○○サービス（株）の範囲”
みたいに。

で、参照は
　INDIRECT(VLOOKUP(A1,対応表シート!A:B,2,0))
こんな感じになると思う。

それにこのほうが管理しやすいと思う。

No.2 回答者： tom04 回答日時： 2018/12/03 17:02

こんにちは！

名前定義をする場合、半角というよりカッコなどが使えないようですね。
一案です。（やり方だけ・・・）

↓の画像のようにSheet2に表があるとします。
Sheet1のA列のリスト設定でSheet2のA1～C1セルを選択しています。
そしてB列のリストの元の値の欄に
=OFFSET(Sheet2!$A$1,1,MATCH(A1,Sheet2!$1:$1,0)-1,COUNTA(OFFSET(Sheet2!$A:$A,,MATCH(A1,Sheet2!$1:$1,0)-1,,1))-1)

という数式を入れると画像のような感じでリスト設定が可能です。

※　あくまでアップした画像、Sheet2の配置での数式になりますので、
配置が換わると当然数式も変わってきます。m(_ _)m

この回答へのお礼

図で解説して頂きありがとうございます！
こちらの方法でも試しましたが希望通りの内容が実装出来ました！
ありがとうございました。

お礼日時：2018/12/05 11:18