Wondering if anyone knows the solution to this.
Im running Excel 2007
I have calculations set on automatic but it's sort of behaving as if it isn't.
Basically every month a macro is run and it adds in a new column to look up data for that new month.
Simple terms column B formulas will look up the date in B1 and then find the data in a different spreadsheet, C is the exact same formulas only B is now C. Etc etc. The macro adds the new column and moves the formulas over. So itll run add the date to column D1, then put the formulas in the remainder of column D to refernce D1. The formulas added are correct but some aren't updated, they are still the data as if it was referencing column C. If I manually drag C over to D then the formulas update the values (but the actual formula is the same).
It seems the simple formulas update ie =D9/D11 but the more complex ones where its looking at the other file using an index(match)) function doesn't update. Having the other file opened or closed doesn't change the results.
As I said automatic updating is on, F9 does nothing, Shift+F9 does nothing, ctrl-shift-F9 does nothing.
I'm stumped.
Im running Excel 2007
I have calculations set on automatic but it's sort of behaving as if it isn't.
Basically every month a macro is run and it adds in a new column to look up data for that new month.
Simple terms column B formulas will look up the date in B1 and then find the data in a different spreadsheet, C is the exact same formulas only B is now C. Etc etc. The macro adds the new column and moves the formulas over. So itll run add the date to column D1, then put the formulas in the remainder of column D to refernce D1. The formulas added are correct but some aren't updated, they are still the data as if it was referencing column C. If I manually drag C over to D then the formulas update the values (but the actual formula is the same).
It seems the simple formulas update ie =D9/D11 but the more complex ones where its looking at the other file using an index(match)) function doesn't update. Having the other file opened or closed doesn't change the results.
As I said automatic updating is on, F9 does nothing, Shift+F9 does nothing, ctrl-shift-F9 does nothing.
I'm stumped.