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

質問

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

noname#52504 · ベストアンサー

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を使う

お礼

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

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

回答No.2

hana-hana3

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

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

回答No.1

hana-hana3

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

補足

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

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