TM1 Forum

We are running TM1 9.4 MR1 on a X64 box and using Excel 2007. On occasions, I will have users reporting a “sleeping cell issue”, that is a cell or group of cells in excel with valid and verified TM1 formula will display #value. Pressing F9 will still display the #value but if users go to the specific cells and press F2, the #value will disappear and return the expected result.

I’ll appreciate any thoughts on how to resolve such occurrences.

Thank you

jorelb wrote:We are running TM1 9.4 MR1 on a X64 box and using Excel 2007. On occasions, I will have users reporting a “sleeping cell issue”, that is a cell or group of cells in excel with valid and verified TM1 formula will display #value. Pressing F9 will still display the #value but if users go to the specific cells and press F2, the #value will disappear and return the expected result.

I’ll appreciate any thoughts on how to resolve such occurrences.

My first thought would be that one or more of the arguments in the TM1 formula depends on the value in another cell which isn't updating correctly in a standard recalculation action. This is often seen with DBRWs which rely on other DBRWs, though that will generally result in a *KeyErr error rather than a #Value one so it may be a more conventional Excel reference issue.

I haven't checked whether this is still the case in 2007 but in earlier versions of Excel you could press [Ctrl]+[Shift]+[Alt]+[F9] to not only do a full calculation, but to completely rebuild the workbook's calculation dependency tree. It might still be worth a shot. I'd also recommend checking the formulas that you have in the sheet to ensure that the paths aren't overly complicated. (Sheet 3 depends on a value in sheet 1 depends on a calculated value in sheet 3 depends on an entry in sheet 2... that kind of thing.)

Alan, thanks much - it works!

Jose

Not sure if this is related, but we had a similar #value issue with Excel 2003 & TM1 9.1 SP3.

It didn't happen often and when it did it effected only a very few users. Our hypothesis was that somehow Excel's cache of TM1 references got out of sync with TM1 since users reported this issue when dimensions were rebuilt including new elements, and so we ran the M_CLEAR TM1 macro function. This seemed to do the trick.

Hi All,

Very interresing posts,
I have hit this issue thousand times.

I usually struggled giving any kind of logical answer to my users...
Picking a working formulas and dragging it over the "broken" one usually did the trick but i would have like to have something more rational.

I like the assumption that the excel cache get somehow disconnected from The TM1 references...

Next time i got hit by this i will try both your tricks :
- [Ctrl]+[Shift]+[Alt]+[F9] as a user sort itself out fix
- the "M_Clear" integrated in an admin tool

Thanks,

Kind regards,
Olivier

I am relieved to find out that my users are not the only one experiencing this issue and that it may not be a TM1 but an excel problem. Since TM1 uses Excel as a front end, how can we clearly determine whether the problem is with TM1 or Excel? Another frequent error message I get from excel is “Excel found unreadable content in Filename.xlsx. Do you want to recover the contents of this workbook?” I click ok then I get another dialogue box that says: 1) Removed Records: Share formula from /xl/worksheets/sheet3.xml part 2) Removed Records: Formula from /xl/calcChain.xml part (Calculation properties). When the worksheet opens, some of the calculated fields (DBRWs and Excel calculations) are converted to values. I googled the error message and I found a number of items about the error message which are not related to TM1 – it also happens to users who do not have TM1 at all.

Thanks,

Jose

My first post! Hello World.

I can tell you what I do. I have an Excel VBA macro to calculate a selection.

Selection.Calculate

I always program a button to run that macro on the users' computers. (I have a similar button to recalc the entire active sheet.)

Because calculation is always set to Manual (no interation), the users have to request a recalc, and yes, they run into the problem you see where Excel skips the "sleeping" cell. If they highlight the area of interest, then hit the macro for selection.calculate, it forces a calculation in those cells.

An added benefit is that you can recalc just the area of interest, instead of waiting for a giant workbook to grind through. Users like that a lot. Me too. Works for us.