除非我单击公式并按Enter，否则Excel公式不会更新 [英] Excel formulas don't update unless I click on the formula and press enter

问题描述

我有一个Excel工作簿，其中的公式工作正常，除了两个表列在打开时不会重新计算. Excel工作表为2003 xls(2010年为兼容模式).

I have an excel workbook with formulas that works fine except for two table columns which don't recalculate on opening. The excel sheet is 2003 xls (compatibility mode in 2010).

我已经将工作簿设置为自动计算，并且包含公式的单元格设置为常规格式.我尝试单击空白单元格并使用粘贴特殊的加法或乘法，并且尝试仅粘贴其他可用单元格的格式，但这都不能解决我的问题.

I've my workbook is set to calculate automatically and the cells containing the formulas are formatted as general. I've tried clicking a blank cell and using paste special add or multiply and I've tried pasting just the formatting of other cells that work but none of this has fixed my problem.

类似地，单击立即计算"或计算工作表"不起作用.起作用的是单击单元格公式，然后按Enter.也可以另存为xlsm，然后重新打开即可(但是不幸的是，这不是解决方案).

Similarly clicking Calculate Now or Calculate Sheet doesn't work. What does work is clicking in the cell formula and pressing enter. Also saving as xlsm and then reopening works (but this is not a solution unfortunately).

我有宏，但是它们都不影响这些公式所在的工作表或计算样式.

I have macros but none of them affect the worksheet these formulas are in or the calculation style.

还有什么可以尝试的吗?

Anything else I can try?

受影响的公式如下:

=IF($I2<>"D/E",$J2+($K1-$L1),$J2)

=(ROUND(FixedQuotaGuide!$K2,0))

推荐答案

如果这对其他人有帮助，我最终可以通过在公式的每个部分中包含制表符引用来使它正常工作，如下所示:

In case this helps anyone else I finally got this to work by including the tab reference in each part of the formula like so:

原始公式:

=IF($I2<>"D/E",$J2+($K1-$L1),$J2)

工作公式:

=IF(FixedQuotaGuide!$I2<>"D/E",FixedQuotaGuide!$J2+($K1-$L1),FixedQuotaGuide!$J2)

这篇关于除非我单击公式并按Enter，否则Excel公式不会更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！