[[20191028173152]]　『ブックを閉じた状態でINDIRECT関数を使ったような』（sato）　ページの最後に飛ぶ

『ブックを閉じた状態でINDIRECT関数を使ったような動作をさせたい』（sato）

 お世話になります。
 =IF($B8="","",INDEX(INDIRECT("'[AAA.xlsx]"&REPLACE(CELL("filename",A4),1,FIND("]",CELL("filename",A4)),"")&"'!$B:$G"),MATCH($B8,INDIRECT("'[AAA.xlsx]"&REPLACE(CELL("filename",A4),1,FIND("]",CELL("filename",A4)),"")&"'!$C:$C"),0),6))
 という計算式を用いているのですが、INDIRECT関数は参照先のブックを閉じているとエラーになってしいます。
 ブックを閉じた状態でも正しく結果が表示されるような動作を行うにはどのような方法を用いればよいでしょうか？

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

過去ログを見れば分かると思いますが、INDIRECT関数は開いているブックにしか使えません。
[[20181017112724]]　『別ファイル内のセルを参照するための入力方法につ』（和歌）
[[20180320172515]]　『別データのセル文字列を持ってくる式について』（たぬき）
[[20161017121642]]　『リンク先の変数化』（フィリップ）

＞ブックを閉じた状態でも正しく結果が表示されるような動作を行うにはどのような方法を用いればよいでしょうか？
参照したいシートの複製を自ブック内に用意しておくとか、ＶＢＡを使ってみるとかのアプローチになろうかと思われます。

（もこな２） 2019/10/28(月) 18:16

 ありがとうございます。
 INDIRECT関数が開いているブックにしか使えないのは存じておりますので、同様の動作を閉じたブックに対して行うにはどのような方法があるのか知りたくて質問した次第です。
 シートの複製を用意したくはありませんので、VBAを使う方法について詳しくご教示願えれば幸いです。
（sato） 2019/10/30(水) 16:50

こちらをどうぞ。
http://officetanaka.net/excel/vba/tips/tips28.htm

（もこな２） 2019/10/30(水) 19:18

 INDIRECT関数とExcel4Macroとどんな関係があるのだろう？
（はて？） 2019/10/30(水) 20:35

 このトピの流れなら大いに関係あるんじゃないですか？

（半平太） 2019/10/30(水) 21:06

 数式を見ると
 AAA.xlsx は数式を使用しているブックと同一フォルダにある。
 AAA.xlsx の対象シート名は数式を使用しているシート名と同名。

 上記に誤解が無ければ

 Sub test()
     Dim fn As String, x As String
     fn = "'" & ThisWorkbook.Path & "\[AAA.xlsx]" & ActiveSheet.Name & "'!"
     With Range("b8", Range("b" & Rows.Count).End(xlUp))
         x = .Cells(1).Address(0, 0)
         With .Offset(, 2)   '<--- (, 2)はB列から右に2列目
             .Formula = "=iferror(index(" & fn & "b:g,match(" & x & "," & fn & "c:c,0),6),"""")"
             '.Value = .Value  '<--- 数式を値にするなら ' を削除
         End With
     End With
 End Sub

（seiya） 2019/10/30(水) 22:29

 >AAA.xlsx は数式を使用しているブックと同一フォルダにある。
 >AAA.xlsx の対象シート名は数式を使用しているシート名と同名。

 ご推察のとおりなのですが、教えて頂いたマクロを実行してもエラーは出ませんが特にセルの値も変わりませんでした。
（sato） 2019/11/01(金) 10:04

 D列に入力された数式をみて、想定通りの数式になっているか確認してください。
（seiya） 2019/11/01(金) 11:04

 ありがとうございます。
 数式はD列に入力されていたんですね。少しだけ数式を変更して思い通りの結果が得られました。
 素晴らしいマクロをありがとうございます。
（sato） 2019/11/01(金) 17:32

コメント返信：

[ 一覧(最新更新順) ]

[[20191028173152]] 『ブックを閉じた状態でINDIRECT関数を使ったような』（sato） ページの最後に飛ぶ

[[20191028173152]]　『ブックを閉じた状態でINDIRECT関数を使ったような』（sato）　ページの最後に飛ぶ