セルに入力した文字と別シートリストの見出しが合致したらリストをドロップダウンリスト表示させたい

セルに入力した所属名を別シートにあるリスト見出しから検索し、合致した列の列（見出しより下）をドロップダウンリスト表示させたいです
段階を踏んだリストの絞り込みではなく、入力したセル内で表示させたいです
イメージでは日本語入力enterで選択候補が表示されるイメージが近いです

(ピックアップSHEET)
　A　　　　B　　　　C　　　　　D　・・・・
対象者1　対象者2　対象者3　対象者4　対象者は15まであります

※各対象者のセルに部署名を入力すると、その場で該当する所属者をドロップダウンリスト表示したい

(名簿SHEET)
　Ａ　　 Ｂ　　　Ｃ
物流　　調達　 メンテ・・・・・部署は２４個あります
●●　　●●　　●●・・・氏名がフルネームで入っています
↓ ↓　　　↓・・・所属者がリストになっております


お力添えをお願い致します

No.2 ベストアンサー 回答者： ママチャリ 回答日時： 2018/06/23 10:37

添付画像のようなレイアウトを前提して説明させていただきます（各リストは名前の定義がされている前提です）。

一番簡単な方法は、E1セルの入力規則に「=INDIRECT(E1)」を設定することです。しかし、これだとE1セルに「メンテ」を入力して確定した後、もう一度E1セルに戻ってリストを選択する必要があります。使い勝手が悪いですが、標準的なExcelの使い方としては、これが限界ではないでしょうか。
そこで、VBAの登場ですが、それなりのスキルが必要になりますので、興味があれば次の事を試してみて下さい。

まず、E1とF1セルに入力規則でリストを設定します。その際、「無効なデータが入力されたらエラーメッセージを表示する」チェックを必ず外してください。
次に、シートモジュールに次のVBAを張り付ければ完成です。

まず、E1セルで試してみて下さい。UEHさんのご希望通りに動作すると思います。
次に、F1セルで試してみて下さい。ちょっと機能を拡張してみました。個人的にはこっちの方が便利だと思います。

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exit_sub
With Target(1)
If .Validation.Type = xlValidateList And _
.Validation.Formula1 <> "=" & .Value Then
.Validation.Modify Formula1:="=" & .Value
.Select
SendKeys "%{DOWN}"
End If
End With
exit_sub:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub
Target.Validation.Modify Formula1:="=A1:C1"
End Sub

この回答へのお礼

ありがとうございます！
動きを確認しましたが、思っていた通りです
重ねての質問で申し訳ありません
あとは、自身の帳票にコンバートしてみますが所属部署リストが違うシートにある場合は
Target.Validation.Modify Formula1:="=A1:C1"はシート名+セル範囲にすればよろしいでしょうか

お礼日時：2018/06/25 14:12

No.1 回答者： moon-and-star 回答日時： 2018/06/22 16:00

少しわかりにくいのですが･･･

> 入力したセル内で表示させたいです
入力した時点で、セルに設定してあった関数は壊れます。
VBAなら？？？ですが、普通に関数では無理があります。