Formula not calculating automatically

[ian_noobster]

Hi,

=SUMPRODUCT((Sheet1!$B:$B='Sheet2'!$A2)*(Sheet1!$I:$AL='Sheet2'!C$1))

The formula above is forcing me to select the cell then press enter on each individual cell to calculate it. I've checked the formula calculation is on Automatic and the cells are formatted to general.

Can anyone advise me why this would be happening and if there is a way to resolve this. I'm using this formula multiple times and clicking each one to make the forum la work isn't really an option

Thanks

Ian

 

[sheetspread]

Do you have to search the whole column? That can be very slow.

 

[ian_noobster]

No could easily set it to a range of

=SUMPRODUCT((Sheet1!$B1:$B100='Sheet2'!$A2)*(Sheet1!$I1:$AL100='Sheet2'!C$1))

 

[RoryA]

Do you have any circular reference warnings?

 

[ian_noobster]

Not that I can see, would the errors appear in the status bar?

 

[sheetspread]

Not that I can see, would the errors appear in the status bar?

When you open the file there would be a warning

Are all formulas manual, even simple ones of single cells?

 

[RoryA]

Yes, there should be a warning in the status bar, assuming you haven't enabled iterations.