Excel formulas don't update unless I click on the formula and press enter

1
votes

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.

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?

Affected formulas are as follows:

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

=(ROUND(FixedQuotaGuide!$K2,0))
1
excelformulas
hmm that is strange. As I can see, you have done almost everything... May I see your excel file? If yes,c an you upload it to say wikisend.com and share the link here? – Siddharth Rout
I put some values in Col J and the Col K updated without any problems... – Siddharth Rout

1 Answers

1
votes

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:

Original formula:

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

Working formula:

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