Cells not calculating unless edited

Post Reply
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Cells not calculating unless edited

Post by NPTM1 »

Hi all

I'm not sure if this has come up before, but I've got a strange situation with a slice. I've got some DBRW cell forumulae which update ok, however SOME dependant cells don't seem to want to recalculate (e.g. a Sum formulae).

I've tried using Shift+F9, F9 and Alt+F9 but these dependant formulae don't seem to want to change. The only way I can get them to update is by selecting the cell and hitting F2 then Enter.

This isn't an overly complex workbook, and strangely other sheets work fine, it's just some formulae on this one tab that are playing up.

Has anyone come across this before or know a better way of recalculating the whole workbook?

Thanks in advance.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Cells not calculating unless edited

Post by Mark RMBC »

Stab in the dark, but in the cells for the sum formulae, could they be formatted to text?

Have you tried inserting another column, ensuring the format is general and recreating the sum formula and testing that?
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Re: Cells not calculating unless edited

Post by NPTM1 »

They seems to work as normal after edit/enter. It was only 4 cells that were affected on the sheet. I guess I was hoping to find out the cause to avoid having this happen in future.

I know that I grouped tabs together to do a bulk edit, and maybe this triggered a bug. It's possible this was an excel issue and not a perspectives one.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cells not calculating unless edited

Post by tomok »

NPTM1 wrote: Tue Nov 21, 2017 12:38 pm I've got some DBRW cell forumulae which update ok, however SOME dependant cells don't seem to want to recalculate (e.g. a Sum formulae).
What exactly do you mean by "SOME dependant cells"? Do you have nested DBRW formulas. By that I mean the result of one DBRW is used in another DBRW? If that is the case it is a no-no. You will need to change the source DBRW to a DBR.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Cells not calculating unless edited

Post by tm123 »

Is this behavior in TM1Web or Perspectives?

Also, are they ruled cells, excel formulas, or TM1 Consolidations?
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Cells not calculating unless edited

Post by Alan Kirk »

NPTM1 wrote: Tue Nov 21, 2017 2:08 pm They seems to work as normal after edit/enter. It was only 4 cells that were affected on the sheet. I guess I was hoping to find out the cause to avoid having this happen in future.

I know that I grouped tabs together to do a bulk edit, and maybe this triggered a bug. It's possible this was an excel issue and not a perspectives one.
If you ever get this again, try Ctrl+ Shift + Alt + F9. (That is, every modifier key.) It not only recalculates but rebuilds the dependency tree as well. From what you're describing it's possible that your multi-sheet edit caused a problem in the tree. It shouldn't in theory, but in practice... y'never know.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
pandinus
Posts: 78
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Re: Cells not calculating unless edited

Post by pandinus »

This can also happen when you somewhere have a DBRW formula which points to an intersection in the cube that is not part of the VIEW formula that you refer to in the DBRW.
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Re: Cells not calculating unless edited

Post by NPTM1 »

The CTRL+ALT+SHIFT+F9 worked, definitely something I'll keep in mind, many thanks Alan.
Post Reply