エクセルのリストボックスについて

質問

アドレス帳から、ＦＡＸ送信状みたいなものをつくってまして
うまくいかないので、どうか力をかしていただけないでしょうか？

　　　 Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ
１　　宛先　　　担当者１　　担当者２　　担当者３
２　関東銀行　　　佐藤　　　　鈴木　　　　田中
３　近畿銀行　　　山田　　　　山口　　　　太田
４　中部銀行　　　後藤　　　　浜田　　　　松本

といった感じにデータを作ってます。
別のシートに送信状をつくって、あるセルの上にリストボックスを
作成して上のデータを登録し、セルにＶＬＯＯＫＵＰで表示させていますが、宛先は問題なくできるのですが、担当者を別のセルにリストボックスを作り、３名選択させるようにするには、どうしたらよいでしょうか？

宛先は、追加や削除する可能性もあるので、並び替えはあります。
担当者も追加や削除する可能性もあります。
入力規則で名前の定義の設定してやってみたのですが、
宛先（企業名）が長い場合、使えません＞＜；

よい知恵を貸してください。お願いします。

つまらない質問とは思いますが、どうかご指導お願いします。

回答No.1

noname#79209 · ベストアンサー

とりあえず、同一シートなら可能です。
入力規則の参照元は、同一シートでなければならないので．．．
ご質問の表がA1からE4に入っているとします。
担当者を選ぶ為の会社名が表示されているセルを「E1」、担当者を選択させるセルを「F1」とします。
F1の入力規則に、
「入力値の種類」に
　「リスト」
「元の値」に
　=OFFSET(A1,MATCH(E1,A2:A4,0),1,1,3)
で可能でした。

お礼

どうもありがとうございます。
オフセットとマッチに組み合わせまでは、頭が働きませんでした＾＾；
勉強不足です＞＜；
これからも、もっとエクセルを極めます。
今回は本当にありがとうございました。

回答No.2

noname#79209

#1です。
強引ですが、一応ワーク用のセルを使えば別シートでも可能になりました。
　　　 Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ
１　　宛先　　　担当者１　　担当者２　　担当者３
２　関東銀行　　　佐藤　　　　鈴木　　　　田中
３　近畿銀行　　　山田　　　　山口　　　　太田
４　中部銀行　　　後藤　　　　浜田　　　　松本
上記が[Sheet1]にあるとします。
宛名と担当者を入力するシートを[Sheet2]とします。
[Sheet2]の会社名の入るセルを[A1]、担当者を選択するセルを[B1]とします。
さらに、ワーク用のセルを[Sheet1]のセル[E1]とします。
このセル[E1]に
=ADDRESS(MATCH(Sheet2!A1,A2:A4,0)+1,2,1,TRUE,"sheet1")&":"&ADDRESS(MATCH(Sheet2!A1,A2:A4,0)+1,4,1,TRUE)
という数式を入力し
担当者を選択する[Sheet2]のセル[B1]の入力規則に
[入力値の種類]を[リスト]
[元の値に]
=INDIRECT(INDIRECT("sheet1!$E$1"))
とすれば可能でした。

お礼

INDIRECTまではやったのですが、２つ利用するんですね＾＾；
こちらも使えますね。
貴重な時間までさいて回答してくださり、恐縮です。
今回はどうもありがとうございました。