別ファイルを参照しながらシート名を動的に

Excelにて、VLOOKUP関数を使用し別ファイルを参照してます。
(こちらをAファイルとします)
別ファイル（こちらをBファイルとします）にはいくつかシートがあり、Aファイルに入力された値によって、Bファイルを参照するシートを変更したいのですが、うまくいきません。

AファイルはC:\workに
BファイルはC:\work\workにそれぞれ配置してあります。

AファイルのセルA1には任意の値を入力可能で、その値によって参照するBファイルのシートを変更できます。
VLOOKUP(B1,INDIRECT("'C:\work\work\[Bファイル.xls]"&A1&"シート'!$A$1:$B$100")
Aファイルに上記のような関数を書いても、#REFと表示されてしまいます。
どこがおかしいのでしょうか？

#AファイルとBファイルを同時に開いている状態だとうまく表示されました。

No.3 ベストアンサー 回答者： deus_ex_machina 回答日時： 2008/02/04 23:53

VLOOKUPの問題というよりもINDIRECTの問題ですね。

INDIRECT関数は閉じているブックへの参照を作ることはできません。

代替案としては…

Ａ案　
　各シート別の参照をそれぞれ用意しておいて、CHOOSEで選ぶ。

　１．Ａファイル上で、Ｂファイルへの参照をそれぞれ名前定義する
　　挿入＞名前＞定義
　　甲：='C:\work\work\[Bファイル.xls]甲シート'!$A$1:$B$100"
　　乙：='C:\work\work\[Bファイル.xls]乙シート'!$A$1:$B$100"
　　丙：='C:\work\work\[Bファイル.xls]丙シート'!$A$1:$B$100"
　　丁：='C:\work\work\[Bファイル.xls]丁シート'!$A$1:$B$100"

　２．数式を下記のように設定
　　=VLOOKUP(B1,CHOOSE(MATCH(A1,{"甲","乙","丙","丁"},0),甲,乙,丙,丁),2,0)

Ｂ案　
　Ｂファイルのデータを、同一シートの単一の範囲として取得できる形に整形し、
　INDEX,MATCH等を使って引く。
　※VLOOKUPでは検索列を指定できないことに注意
　※OFFSETでは閉じたブックを参照できないことに注意

　=INDEX(INDEX(【データ範囲】,,【戻値列】),MATCH(B1,INDEX(【データ範囲】,,【検索列】),0))

　【戻値列】，【検索列】はA列の値とMATCHを使って数値で指定する

Ｃ案
　Ｂファイルのデータを、
　Ａファイルの非表示のシートにまるまる引っ張っておいて、
　そこから選ぶ。　

Ｄ案
　VBAを使う

この回答へのお礼

代替案まで提示していただき、本当にありがとうございました。
色々方法があるんですね。
締め切りまでもう少し時間があるので、
教えていただいた方法で何が一番ベストなのか試してみたいと思います。

ありがとうございました。

お礼日時：2008/02/05 11:06

No.2 回答者： hana-hana3 回答日時： 2008/02/04 16:06

＞VLOOKUP以外の関数を使ったとしても、

そうです。
どんな関数を使っても開いてないファイルから動的に値を取得する事はできません。

No.1 回答者： hana-hana3 回答日時： 2008/02/04 15:26

手動（若しくはマクロ）でリンクの更新を行わない限り不可能です。

この回答への補足

ご回答ありがとうございます。

続けざまの質問になってしまい恐縮ですが、
VLOOKUP以外の関数を使ったとしても、
表題の内容を実現する為には、リンクの更新を行わないと不可能なんでしょうか？

補足日時：2008/02/04 15:38