• ベストアンサー

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

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ファイルを同時に開いている状態だとうまく表示されました。

質問者が選んだベストアンサー

  • ベストアンサー
noname#52504
noname#52504
回答No.3

VLOOKUPの問題というよりもINDIRECTの問題ですね。 INDIRECT関数は閉じているブックへの参照を作ることはできません。 代替案としては… A案   各シート別の参照をそれぞれ用意しておいて、CHOOSEで選ぶ。  1.Aファイル上で、Bファイルへの参照をそれぞれ名前定義する   挿入>名前>定義   甲:='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"  2.数式を下記のように設定   =VLOOKUP(B1,CHOOSE(MATCH(A1,{"甲","乙","丙","丁"},0),甲,乙,丙,丁),2,0) B案   Bファイルのデータを、同一シートの単一の範囲として取得できる形に整形し、  INDEX,MATCH等を使って引く。  ※VLOOKUPでは検索列を指定できないことに注意  ※OFFSETでは閉じたブックを参照できないことに注意  =INDEX(INDEX(【データ範囲】,,【戻値列】),MATCH(B1,INDEX(【データ範囲】,,【検索列】),0))  【戻値列】,【検索列】はA列の値とMATCHを使って数値で指定する C案  Bファイルのデータを、  Aファイルの非表示のシートにまるまる引っ張っておいて、  そこから選ぶ。  D案  VBAを使う

rx-8_excel
質問者

お礼

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

その他の回答 (2)

  • hana-hana3
  • ベストアンサー率31% (4940/15541)
回答No.2

>VLOOKUP以外の関数を使ったとしても、 そうです。 どんな関数を使っても開いてないファイルから動的に値を取得する事はできません。

  • hana-hana3
  • ベストアンサー率31% (4940/15541)
回答No.1

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

rx-8_excel
質問者

補足

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

関連するQ&A

専門家に質問してみよう