Excel 2段階のプルダウンリスト(名前定義では無い)

Question

###前提・実現したいこと
物件プルダウンリスト１で選んだ物件に対応して、その物件に入っている業者のみプルダウンリスト２に表示したい
・リスト１・・・物件A、物件B、物件Cの3件「物件シート」で他情報も併せて管理
・リスト２・・・業者１、業者２、業者３の3社「業者シート」で報酬計算方法などと併せて管理
→1対1ではなく、同じ物件に複数業者、業者が複数物件に入ることがある
![イ

名前定義では、業者シートの作りが異なり、実現したい内容と異なってました。
最悪は2段階目のリストのために「名前定義シート」を作って、そこを選ばせる。
それから入力シートに出力された業者を指定してVLOOKUPで付随情報を使うか・・・
業者が増えた時のメンテナンスが増えるので、出来れば図の3シート内で納めたいと思ってます

###試したこと
ネットで探してみましたが、どこを見ても「名前定義＋INDIRECT関数」で、項目選ばせて終わりの方法しか見つけられませんでした。
その物件担当者が関係ある業者だけ表示して選ばせて、それに対応した計算をさせたいので名前定義での項目の作り方では、
やりたいことが実現できませんでした。。。

書きながら思いましたが、名前定義シート作るしかないのかな・・・

Answer 1

INDIRCTを使った2段階のプルダウンリスト表示方法を知らずにVBAを使った方法を書いてしまいました。2段階のプルダウンは、INDIRCTを使う方法でもよいですね。勉強になりました。
プルダウンで業者を選んで、計算方法を自動的に表示するには、決定された物件と業者を結合し、このキーを元に、VLOOKUP(業者シートにも、物件と業者を計算式など結合した列を用意しておく)で取り出せると思いますが、如何でしょうか？

Comments

[morikawa0208]

お返事が遅くなり申し訳ございません。私事でお休みしてました。

最初にいただいたVBAも試してみたのですが、理解が足りず現状うまく動かせておりません・・・
たしかにINDIRECT関数でやりたいことは実現できるんですが、業者が増えた時に2シート追加しないといけないのが引っかかってました(業者シートとINDIRECT用のシート)

でもINDIRECT関数のほうがやってることが分かりやすいので、関数でやってみようと思います。

私のこんな質問でVBAが組める人は本当に憧れます、日々勉強ですね！
ご回答ありがとうございました！

Answer 2

他にも方法があるかと思いましたが、私ならということでお答えします。
やはりVBAが一番簡単かと思います。

入力シートの「今回物件」列（この列には、物件シートの物件列が入力規則として設定されていることを前提にしています）で、値が変更されたら、そのタイミングで業者シートから対象となる業者名のリスト(例えば、今回物件が物件Aであれば、業者1,業者2)を対応する行のB列に設定すれば、対応する業者名がドロップダウンリストで表示されるようになります。

サンプルプログラムは以下の通りです。不明点があれば、お尋ねください。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    
    r = Target.Row
    ' 変更されたセルがA列のセル１つだったら、
    If Target.Count = 1 Then
        If Target.Column = 1 Then
            Dim i As Integer
            Dim s As String
            
            ' 変更されたA列の今回物件と文字列を業者シート内の物件列(B列)からさがし、
            ' 見つかった値をカンマで繋げてsに代入する
            With Worksheets("業者シート")
                s = "入力して下さい"
                For i = 2 To 1000
                    If .Range("B" & i).value = Target.value Then
                        s = s & "," & .Range("A" & i).value
                    End If
                Next i
            End With
            ' 例えば、物件名が「物件A」の場合、変数sは次のようになる
            ' s = "入力してください,業者1,業者2"
            With Target.Worksheet.Range("B" & r)
                    ' 入力シートの対応する行のB列の入力規則にsを設定します。
                    With .Validation
                    .Delete
                    .Add xlValidateList, Formula1:=s
                End With
                ' 入力規則を設定したセルにも"入力してください"を指定しました。
                .value = "入力してください"
                ' 入力規則を設定したセルにフォーカスを移動します。
                .Select
            End With
        End If
    End If
End Sub