こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2008-02-04 15:21:01

QNo.3743796

すぐに回答ほしいです

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

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

投稿日時 - 2008-02-04 23:53:22

お礼

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

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

投稿日時 - 2008-02-05 11:06:54

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

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

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

投稿日時 - 2008-02-04 16:06:59

ANo.1

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

投稿日時 - 2008-02-04 15:26:46

補足

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

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

投稿日時 - 2008-02-04 15:38:09

あなたにオススメの質問