使用INDIRECT函数跨工作簿引用单元格返回#REF!错误求助

问题原因 #

INDIRECT函数存在内置限制：无法解析未打开的外部工作簿引用路径。只有当目标工作簿KVR 4.0.xlsm处于打开状态时，它才能正常识别A1中的路径并返回对应单元格值。

解决方案 #

• 临时解决：打开KVR 4.0.xlsm工作簿，刷新B1单元格的公式，此时=INDIRECT(A1)就能正常返回结果。
• 永久解决（支持关闭目标工作簿时引用）：
  1. 直接使用外部引用公式：在B1输入='C:\Users\thekv\OneDrive\Documents\accessmydocs\[KVR 4.0.xlsm]SheetOne'!$E$32，无需INDIRECT，Excel支持直接引用关闭的工作簿。
  2. 自定义VBA函数：
     按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

     Function GetClosedCellValue(filePath As String, cellRef As String) As Variant
         Dim wb As Workbook
         Dim sheetName As String
         Dim cellAddress As String
         
         ' 拆分工作表名和单元格地址
         sheetName = Left(cellRef, InStr(cellRef, "!") - 1)
         cellAddress = Mid(cellRef, InStr(cellRef, "!") + 1)
         
         On Error Resume Next
         ' 只读打开工作簿
         Set wb = Workbooks.Open(filePath, ReadOnly:=True, UpdateLinks:=False)
         If Err.Number = 0 Then
             GetClosedCellValue = wb.Sheets(sheetName).Range(cellAddress).Value
             wb.Close SaveChanges:=False
         Else
             GetClosedCellValue = "#REF!"
         End If
         On Error GoTo 0
     End Function
     

     回到Excel，在B1输入公式：=GetClosedCellValue("C:\Users\thekv\OneDrive\Documents\accessmydocs\KVR 4.0.xlsm", "SheetOne!$E$32")，即可在目标工作簿关闭时获取数据。

内容的提问来源于stack exchange，提问作者user6879880