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.
Cells not calculating unless edited
-
- 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
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?
Have you tried inserting another column, ensuring the format is general and recreating the sum formula and testing that?
-
- 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
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.
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.
-
- MVP
- Posts: 2832
- 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
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.
-
- 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
Is this behavior in TM1Web or Perspectives?
Also, are they ruled cells, excel formulas, or TM1 Consolidations?
Also, are they ruled cells, excel formulas, or TM1 Consolidations?
- 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
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.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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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.
-
- 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
The CTRL+ALT+SHIFT+F9 worked, definitely something I'll keep in mind, many thanks Alan.