一致する文字列がある行だけを、別BOOKのプルダウンリストに表示させる方法

質問

OFFICE 2003のエクセルで作成した下記のような３つのＢＯＯＫがそれぞれあり、ＢＯＯＫ１のシートはマスターで、「案件NO」「案件名」「進捗」「メンバ1」「メンバ2」「メンバ3」の項目がある。
ＢＯＯＫ２とＢＯＯＫ３のシートには「案件NO」「案件名」「進捗」「概要」「報告」の項目があります。
例えば、ＢＯＯＫ１のセルD2～F2の中のいずれかのセルに表示されているメンバの名前を参照し、「山田」と名前が入っているセルがある場合は、その同じ行の「A列、B列、C列」のセルをＢＯＯＫ２の「A列、B列、C列」に設定したプルダウンリストの中に表示させる。
（つまり、山田さんの名前が入っている案件情報だけをプルダウンリストに表示する）

また同様に、メンバ欄に「森」が入っている行の「A列、B列、C列」のセルをＢＯＯＫ３の「A列、B列、C列」に設定したプルダウンリストの中に表示させる。
（森さんの名前が入っている案件情報だけをプルダウンリストに表示する）

上記のように、案件マスターを参照し、担当者ごとに作成している別ブックのセルにに設定してあるドロップダウンリストの中に、メンバ欄の中に自分の名前が入っている案件だけをリストアップすることは、可能でしょうか？
※　ＢＯＯＫ１は、自由に編集することができ、数千件のデータが入っている

※　ＢＯＯＫ２とＢＯＯＫ３の「Ａ，Ｂ，Ｃ」各列はドロップダウン設定がされている。

　　ＢＯＯＫ１のデータとリンクしており、自分が担当している案件だけが、ドロップダウンリストの中に表示される。担当以外の情報は表示されない。）
　　列（Ｄ，Ｅ）は、自由に編集できるようになっている。

ＢＯＯＫ１のシート(1)
「案件情報マスター」
　　 A　　　　　　B　　　　　　C　　　　　　D　　　　　　E 　　　　F
案件NO　　　案件名　　　進捗　　　メンバ1　　　メンバ2　　　メンバ3

1　111111　　　あああ　　　　A　　　　山田　　　　吉田 　　　　　森

2 222222　　　　いいい　　　　B　　　　　森　　　　吉田　　　　　佐藤

3 333333　　　ううう　　　　　C　　　　　伊藤 　 　 吉田 　　 　 佐藤

4 444444　　　えええ　　　　　A　　　　　佐藤　　　　 森 　 　 　佐藤

5 555555　　　おおお　　　　　B　　　　　吉田 　　 山田 　 　　 佐藤

----------------------------------------------------------------
ＢＯＯＫ２のシート(1)
「山田さんのブック」
　　 A　　　　　　　B　　　　　　　C　　　　　　D　　　　　　E 　　　　
　案件NO　　　　案件名　　　　進捗　　　　概要　　　　報告

1　11111　　　　 あああ　 　 　 A　　　　　****　　　　○○○　

2　555555　　　　おおお　　　　B　　　　　****　　　　△△△
----------------------------------------------------------------
ＢＯＯＫ３のシート(1)
「森さんのブック」
　　 A　　　　　　　B　　　　　　　C　　　　　　D　　　　　　E 　　　
　案件NO　　　　案件名　　　　進捗　　　　概要　 　 報告

1　11111　　　　あああ　　　　　A　　　　　****　　　　○○○

2 222222　　　　いいい　　　　　B 　　　　****　　　　×××

3 444444　　　　えええ　　　　　A　　　　 ****　　　　□□□
----------------------------------------------------------------
わかりずらい文章ですみません　m(_ _；)m
メニューの「挿入」→「名前」→「定義」名前の定義をすれば、別シートのリストをプルダウンで表示させることができ、またINDIRECT関数を利用すれば３つのドロップダウンリストを連動させることができると言うことは調べてみてわかったのですが…
他のBOOKを参照し、複数のセル（D列～F列）を参照し、そのいずれかのセルに一致する文字列がある場合のみ、その行の複数セル（A列～C列）を　別BOOKに設定しているドロップダウンリストに表示させることができるようにし、各メンバごとに管理できるＢＯＯＫを作成したいと思っております。
よい方法があれば教えていただけると幸いでございます。
宜しくお願い致します。

回答No.2

web2525 · ベストアンサー

Book1に元データが入っているとしますBook名=[元データ]
Book2のSheet1　A1セルにブック名(担当者名）を入力
A2セルに
=IF(OR($A$1=[元データ.xls]Sheet1!D2,$A$1=[元データ.xls]Sheet1!E2,$A$1=[元データ.xls]Sheet1!F2),ROW(),"")
B2セルに
=IF(COUNT(A:A)<ROW()-1,"",OFFSET([元データ.xls]Sheet1!$A$1,SMALL(A:A,ROW()-1)-1,0))
C2セルに
=IF(B2="","",VLOOKUP($B2,[元データ.xls]Sheet1!$A$1:$F$3000,2,0))
D2セルに
=IF(B2="","",VLOOKUP($B2,[元データ.xls]Sheet1!$A$1:$F$3000,3,0))

A2:D2セルを下方向に元データの最終行までコピー

A列は作業セルになるので入力後非表示にしてください。

元データのブックを開いておく必要がありますが関数式のみで各個人のBookは作成可能です。

同一Book内に個人のSheetを作るのであれば、[元データ.xls]の表記は要りません、元データを開かずに利用するにはBookの保存先のフルパスの入力が必要になります。

お礼

早々のご回答をありがとうございました。
このような複雑な関数式を教えていただき、感謝の気持ちでいっぱいでございます。
早速、試してみたいと思います。
また質問等をさせていただくこともあるかと思いますが、宜しくお願い致します。

回答No.1

web2525

条件に一致する行の抽出であれば、関数を駆使して実現は可能ですが、ドロップダウン表示となるとマクロを利用しないと難しいと思います。

入力規制のリストでは作業セルを用いて必要条件に一致する表をすべて作成しないと実現不可と思います、その表自体が目的のシート形状と同じになると思うので、ドロップダウンリストでの表示自体が無意味なものとなると思います。

で、回答案の一例として
Sheet1のどこかに担当者毎の一覧表を別途作成、表の項目ごとに範囲に名前をつけドロップダウンリストで表示させる。

補足

丁寧なお返事ありがとうございます。大感謝です。
初めて質問させていただいたのですが、すぐに回答をいただき驚いております。

ご回答を受けて、下記2点の質問がございます。
　関数を駆使すれば担当者ごとの一覧を抽出して別途一覧表の作成ができるとの事ですが、このＢＯＯＫ１のマスターは毎週更新されるのですが、その都度担当者ごとの一覧を作成するのは時間的に厳しいと思うのですが、一覧を簡単に作成できるような関数式を教えていただくことは可能でしょうか?

また、関数だけでは難しいがマクロを利用すれば実現できるといことでしょうか？もしマクロを使ってできるのであれば、ぜひ挑戦してみたいのですが、分かる方がいらっしゃいましたら　ご教授いただけますでしょうか？
お手数お掛け致しますが、何卒宜しくお願い致します。