Formula Doesn't Calculate

[grovelli]

Any reason why the formulas in cells T10 to T31 (of the attached file) don't give results right away?

[Toranaga]

That formula (in T10) is a array formula that is inserted by pressing Ctrl + Shift + Enter simultaneously.
Enter that array formula in T10 and then drag it down where it is needed.

[HansV]

No, it is not an array formula. HLOOKUP doesn't require that.
I have no idea why the formulas aren't recalculated. Executing

Code: Select all

Application.CalculateFull

from the Immediate window in the VBE fixes it. After that, the formulas are recalculated automatically. By the way, the formula in T10 can be simplified to

=IFERROR(IF(HLOOKUP(T$1,qt!$A$1:$Q$100,$A10,FALSE)="","",HLOOKUP(T$1,qt!$A$1:$Q$100,$A10,FALSE)),"")

(I prefer using a finite range instead of entire columns)

[grovelli]

Simplifying the formula did the trick!
Thank you Hans!

[rory]

Or even just this:

=IFERROR((HLOOKUP(T$1,qt!$A:$Q,$A10,FALSE)&"")+0,"")

[grovelli]

Thanks Rory, that's impressive but unfortunately, in this particular case, Hans formula does calculate while yours doesn't

[grovelli]

Well now even Hans' formula doesn't calculate after trying Rory's formula and then changing it back to Hans'

[rory]

Both work for me after using Ctrl+Alt+F9.

[grovelli]

This is a report, I wouldn't want the people viewing it having to use Application.CalculateFull or Ctrl+Alt+F9

[HansV]

If I enter either your (Giorgio's), my or Rory's formula in T10, it works correctly. Double-clicking the fill handle copies the formula down to T40...

[grovelli]

Yes they all do but there's code in an Access file that fills the qt sheet in this Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qt", "O:\produzioni\stat.xlsx", True, "qt"
and that's when the trouble happens, only for column T cells and it started having this problem only two weeks ago after four years of flawless service.
Interestingly enough, I've been able to recover the Excel file saved right after using Hans' formula and before using Rory's and it works but if I put Rory's in and save it, it stops working and if I try and replace it with Hans' again it doesn't work anymore
I guess one solution could be using Application.CalculateFull when the Excel file opens

[HansV]

I guess one solution could be using Application.CalculateFull when the Excel file opens

That looks like a reasonable workaround.