Forum Discussion
Solved
Weird behaviour when replacing a cached Lambda
I am experiencing some weird behaviour in a production sheet which I have not unfortunately succeeded in replicating in a simple Excel. The problem goes like this, conceptually: Some_Work = LAMB...
For the record, after hours of debugging it transpires that little of what I worried about in the OP had anything to do with the problem. Well - besides perhaps somehow triggering a bug in Excel. We will never know.
Anyway, the killer was an innocuous assignment inside LET like ..., c, Sheet!$S800. That is, Some_Work should refer to column S in which-ever row it is called from. (Some_Work exists in the scope of a Worksheet to provide local context to the Lambdas Big_Work and Little_Work in the scope of the Workbook.)
It failed. Somehow. (I have encountered similar issues in the past with fully relative Names like "=!S5".)
The resolution to the problem was my coding LET( ..., colS, $S:$S, c, @colS, ...).
For the record, after hours of debugging it transpires that little of what I worried about in the OP had anything to do with the problem. Well - besides perhaps somehow triggering a bug in Excel. We will never know.
Anyway, the killer was an innocuous assignment inside LET like ..., c, Sheet!$S800. That is, Some_Work should refer to column S in which-ever row it is called from. (Some_Work exists in the scope of a Worksheet to provide local context to the Lambdas Big_Work and Little_Work in the scope of the Workbook.)
It failed. Somehow. (I have encountered similar issues in the past with fully relative Names like "=!S5".)
The resolution to the problem was my coding LET( ..., colS, $S:$S, c, @colS, ...).