为什么我的Google表格公式不会自动重新计算? [英] Why does my Google Sheets formula not automatically recalculate?
问题描述
我在'摘要'表单中的单元格中有这个公式,它正在等待以名称为日期创建新工作表:
= IFERROR(VLOOKUP($ A3,INDIRECT('& TEXT(BN $ 2,m / d / yyyy)&'!$ E $ 6:$ o $ 100), 11,false),)
然而,当我添加名称为匹配,那么它不会自动重新计算并从新工作表中提取值。
奇怪的是,如果我在上面显示的公式列的左边添加一列,那么它会重新计算......并且一切正常。另一个奇怪的事情是,如果我添加一列离问题列足够远的距离,那么它不会以与在问题列(hmmmm ....)附近添加列相同的方式重新计算。
这里发生了什么?我该如何解决它?似乎Google在这方面存在一些重大问题或其他问题......
是由于> INDIRECT
。 部分解决方法:
= IFERROR(INDIRECT('& C9&'!A1),RAND())
pre>
在您的公式中使用它而不是 INDIRECT
。它仍然不会更新开幕式和闭幕式。但它会更新,无论何时在工作表中的任何地方进行编辑(使其成为一个双重易失性组合,使用 RAND()
)。
另外,请尝试在电子表格设置中每隔6个小时重新计算一次。
PS:您的电子表格可能会烧毁
I have this formula in a cell in a 'Summary' sheet which is waiting for a new sheet to be created with its name being a date:
=IFERROR(VLOOKUP($A3, INDIRECT("'"&TEXT(BN$2,"m/d/yyyy")&"'!$E$6:$o$100"), 11, false), "")
However, when I add the new sheet with the name that would match, then it doesn't automatically recalculate and pull in the values from the new sheet.
The weird thing is if I add a column to the left of this column that has the formula shown above, then it recalculates ... and all is well. Another weird thing is if I add a column far enough away from the column in question then it doesn't recalculate in the same way that it does when I add a column near the column in question (hmmmm....).
What is going on here? And how can I work around it? Seems Google has some major bugs around this area or something ...
This is due to
INDIRECT
.A partial workaround:
=IFERROR(INDIRECT("'"&C9&"'!A1"),RAND())
Use this instead of just
INDIRECT
in your formula. It still won't update on opening and closing. But it'll update, Whenever there's a edit anywhere in the sheet (making this a double volatile combo withRAND()
).Also, Try turning on recalculations every 6 hours or so in spreadsheet settings.
PS: Your Spreadsheet might burn
这篇关于为什么我的Google表格公式不会自动重新计算?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!