在Excel中使用间接函数 [英] using indirect function in Excel

问题描述

我正在使用间接功能创建一个链接到另一个封闭的工作簿。目的是在不打开工作簿的情况下检索值。我在一个地方使用了以下的工作。

  = INDIRECT（[NVOL.xlsb] Sheet1！A& C6）
  

但是，在同一张表中的另一个位置，以下内容不起作用（给出#ref错误）

  = INDIRECT（[MTM.xlsb] Sorted！A& B6）
  

出现 [NVOL.xlsb] 作为替代完整路径，但完整路径不可见。我有两个问题

a）前一种情况（NVOL）的路径以某种方式消失。如何确保它是可见的，以便必要时可以更改路径？

b）如何确保MTM.xlsb在一个完整的路径？

解决方案

两个工作簿必须打开，Excel对语法非常诡异： p>

= INDIRECT（'C：\TestFolder [ABC.xls] NewName'！B& A1）

< （双引号）（单引号）（路径名）（括号）（文件名）（关闭括号）（！）（单元格地址）（双引号）

I am using indirect function to create a link into another closed workbook. The objective is to retrieve the values without opening the workbook. I have used the following in one place which works.

=INDIRECT("[NVOL.xlsb]Sheet1!A"&C6)

However, in another place in the same sheet, the following does not work (gives a #ref error)

=INDIRECT("[MTM.xlsb]Sorted!A"&B6)

It appears [NVOL.xlsb] is taken as a substitute for the full path but the full path is not visible. I have two questions

a) The path in the former case (NVOL) somehow vanished. How do I make it sure it is visible so that the path can be changed if necessary?

b) How to make sure the MTM.xlsb is infact backed by a full path?

解决方案

Both workbooks must be open and Excel is very fussy about the syntax:

=INDIRECT("'C:\TestFolder[ABC.xls]NewName'!B" & A1)

(double quote) (single quote) (path name) (bracket) (file name) (close bracket) (!) (cell address) (double quote)

这篇关于在Excel中使用间接函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！