関数式内で参照するシート名をセルで代用したい

質問

失礼します。

最終ゴールは、シート名にもなっている、バラバラに記入された個々人の目標達成状況を
班ごとに１つのシートにまとめて一目瞭然としたいのです。

この時に、参照するシートからデータを引っ張ることになりますが、
そこで困っていることが、シート名にもなっている個々人の名前の代わりに、
あらかじめ名前を順番に入力したセルを用いたいのです。
しかし、この希望に合致する関数の存在を知りません。
また、どのようなユーザー関数を作ればいいかもわかりません。

そこで、私なりにＶＬＯＯＫＵＰ関数を用いてサンプルを作り、画像添付いたしましたので、
このサンプルを通して妙案を教えていただけましたら幸いです。

ちなみに第一希望は上述の通りなのですが、第二希望もあります。

　（１）一方で、名簿表を作り、番号を割り振る

　（２）この番号をＶＬＯＯＫＵＰ関数の検索値とし、個々人名を名簿表から、
　　　個々人の目標達成状況をその個々人のシートから引っ張ってくる

　（３）他方で、そのシートの名を個人名にできるようなユーザー関数を作って入力する
　　　（それぞれの表の特定のセルに番号を入力すれば、その番号を振った個々人の名前を引っ張ってそれをシート名にするような関数）

という方法も考えたのですが、（３）がどうしても作れません。

回答No.1

mshr1962 · ベストアンサー

C5=VLOOKUP($C5,INDIRECT($B5&"!$B$2:$I$2"),C$4,FALSE)
で下方にコピー

またはシート名"ボブ"の$B$2:$I$2の範囲に「挿入」「名前」「定義」で"ボブ1"と名前をつけて
C5=VLOOKUP($C5,INDIRECT($B5&1),C$4,FALSE)
でも可能

お礼

再びお答えいただくには時機を逸しておりました。申し訳ございません。
今回の質問に対してのお答えでありますから、１００点満点でございます。ありがとうございました。
関連して質問したい内容は、急を要する際に別に質問させていただくことにいたします。

補足

ご回答、誠にありがとうございました。
そして、返事が遅くなりまして申し訳ありませんでした。

遅くなりました理由ですが（言い訳になってしまうのですが）、大きく２つあります。

１つ目は、どうしてダブルクオーテーションマークや＆を用いるのか等、INDIRECT関数について分からないことが多く、十分に理解したうえで今後使っていきたい、ということがあります。

２つ目は、他のエクセル表とリンクができることを知った今、最終ゴールを、いっそのこと「ボブ」とか「トム」と名づけたリンク先ファイル名を作り、そのデータを１つのシートで見られるようなエクセルファイルを作成するところに設定しなおそうか、と考えているからです。

ただ２つ目は、INDIRECT関数でリンク先ファイルを参照するような関数式の作り方が分かりませんので、中継用のシートを作り、そのシート名を「ボブ」「トム」などにして、教えていただいたINDIRECT関数を用いるという代替的な方法もあるわけですが、エクセルファイルの容量がただでさえ１．５ＭＢを超えるものが１．８ＭＢくらいになり、重たくなるので困っています。