エクセル プルダウン連動について

わかる方教えてください
プルダウンを連動するため、ネットを見ながら下記のように設定したいのですが
うまくいきません

シート1に下記のようにデータがあるとします
　A　　　　 B 　　　 C 　　　 　 D
1品番　　 品名 　　 パーツ 　金額
2VB7 カメラ　　レンズ　　\3,000
3VB6 カメラ　　レンズ　　\2,500
4VB7 カメラ　　ボディ　　\1,500


名前の定義を行い　Ａ列を品番として設定
その後　選択範囲から作成を指定し
Ａ列～Ｄ列を全部の範囲を設定し
左端列で設定

シート2

D20のセルを入力規則により名前の定義を行った品番を対象にリスト化

その後　F20に=INDIRECT(D20)を設定を実施

連動するか検証を行ったところ

D20 VB7を選択
F20を狙いとしてはVB7のパーツだけを選択できるようにしたいが、
実際は
F20 カメラ,レンズ,\3000,ボディ,\1,500と全部選べるようになってしまう

パーツだけリスト化するにはどうすればいいのでしょうか？

No.1 ベストアンサー 回答者： tom04 回答日時： 2019/03/05 14:46

こんにちは！

お示しのデータの配置そのままで処理しなければならないのですね。
表の配置を工夫すればINDIRECT関数で出来そうですが、
一案です。
VBAとの併用になります。

Sheet2の作業用の列に「リスト候補」を表示させてみてはどうでしょうか？
配置がよく判らないので、やり方だけ・・・

まず↓のコードをSheet2のシートモジュールにしてください。

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim i As Long, lastrow As Long
　Dim wS As Worksheet
　　If Intersect(Target, Range("D:D")) Is Nothing Or Target.Count > 1 Then Exit Sub
　　　Set wS = Worksheets("Sheet1")
　　　　If Target <> "" Then
　　　　　lastrow = Cells(Rows.Count, "H").End(xlUp).Row
　　　　　　If lastrow > 1 Then
　　　　　　　Range(Cells(2, "H"), Cells(lastrow, "H")).ClearContents
　　　　　　End If
　　　　　For i = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
　　　　　　If wS.Cells(i, "A") = Target Then
　　　　　　　Cells(Rows.Count, "H").End(xlUp).Offset(1) = wS.Cells(i, "C")
　　　　　　End If
　　　　　Next i
　　　　End If
End Sub

次にSheet2のF列リスト表示させたいセルを範囲指定　→　データの入力規則　→　リスト　→　元の値の欄に
=OFFSET(H$1,1,,COUNTA(H:H)-1)

という数式を入れます。

これでD列データの変更があるたびに、D列と対応するSheet2のC列データがH列に表示され、
それをF列のリストに表示させることができます。

※　本来であれば、D列のデータ消去の場合など細かい処理が必要だと思うのですが、
まずはこの程度で・・・m(_ _)m

この回答へのお礼

大変参考になりました　ご教授していただいたやり方で対応してみます

お礼日時：2019/03/05 14:48