如果其他工作簿关闭，公式不会更新 [英] Formulas not updating if the other work book is closed

问题描述

我有下面的公式，它引用了同一文件夹中的另一个工作簿，但是当文件打开时它工作正常，问题是当另一个excel文件关闭时，因为公式不会更新

I have the below formula which makes reference to another workbook in the same folder but when the file is open it just works fine, the problem is when the other excel file is closed because the formula wont get updated

打开其他工作簿时的公式

Formula when the other workbook is open

=IFERROR(COUNTIFS(Auto_Zero.xlsx!MonthDB,B6,Auto_Zero.xlsx!CSRDB,C2),"")

其他文件关闭时的公式

=IFERROR(COUNTIFS('C:UserscsamayoaDesktopQA 
TestAuto_Zero.xlsx'!MonthDB,B6,'C:UserscsamayoaDesktopQA 
TestAuto_Zero.xlsx'!CSRDB,C2),"")

我尝试了很多不同的建议，但当另一个文件关闭时，公式不起作用:(

I have tried a lot of different suggestions and the formula does not wok when the other file is closed :(

推荐答案

当引用关闭的工作簿时，像 COUNTIFS 和 SUMIFS 这样的 Excel 函数不会重新计算.您可以尝试使用像 ADO 代码一样工作的 Excel 查询设计器.允许从封闭的书籍、数据库等中检索.希望这会有所帮助.

Excel Functions like COUNTIFS and SUMIFS does not recalculate when referenced to closed workbook. You could try using Excel Query Designer which work like ADO codes. Allows retrieval from closed books, db etc. Hope this helps.

在不使用高级工具的情况下实现此目的的最简单方法是使用以下代码打开文件，进行计算并关闭文件.不确定这是否有帮助.请将H:My Documents4674576.xlsx"更改为您的源文件路径.将此代码粘贴到 Excel 工作簿的新模块中.运行代码，看看这是否有帮助.

the simplest way to achieve this without using advanced tools will be to use the code below to open the file, do the calculation and close the file back. Not sure if this help. Please change the 'H:My Documents4674576.xlsx' to your source file path. Paste this code in new module of your excel workbook. run the code and see if this helps.

<代码>子加载文件和计算()Dim acWb 作为工作簿Dim wb 作为工作簿设置 wb = Workbooks.Open(Filename:="H:My Documents4674576.xlsx", UpdateLinks:=False, ReadOnly:=True)设置 acWb = ActiveWorkbookActiveSheet.Calculate设置 acWb = 无wb.Close 错误设置 wb = 无

Sub loadfileandCalc() Dim acWb As Workbook Dim wb As Workbook Set wb = Workbooks.Open(Filename:="H:My Documents4674576.xlsx", UpdateLinks:=False, ReadOnly:=True) Set acWb = ActiveWorkbook ActiveSheet.Calculate Set acWb = Nothing wb.Close False Set wb = Nothing

结束子

这篇关于如果其他工作簿关闭，公式不会更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！