index(match) doesn't update unless dragged across. Auto calculate is on

cheswick

New Member
Joined
Mar 16, 2012
Messages
4
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
969
Did you check the formatting of the cells to make sure it's not set on "text"? It needs to be on general...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,997
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top