Circular reference in my worksheet

[2limit2]

When I slice a cube into an Excel worksheet and leave it the way it is, it works fine. When I add some new formula, it gives me a circular reference error. So I delete the formula and hit recalculate (even though I know the formula couldn't have made that error), but the circular reference error stays.

Has anyone experienced this? Is this a known issue for TM1 9.4 and Excel 2010 users?

[Alan Kirk]

When I slice a cube into an Excel worksheet and leave it the way it is, it works fine. When I add some new formula, it gives me a circular reference error. So I delete the formula and hit recalculate (even though I know the formula couldn't have made that error), but the circular reference error stays.

Has anyone experienced this? Is this a known issue for TM1 9.4 and Excel 2010 users?

If it gives you a circular reference error, then you probably do have a circular reference. If you include the exact formulas in your post it may be possible for someone to spot why it's happening.

[2limit2]

Here is the formula I added: =DBRA("tm1 server - apex:Nexus COA",$A12,"Account Name")

[rmackenzie]

When I slice a cube into an Excel worksheet and leave it the way it is, it works fine. When I add some new formula, it gives me a circular reference error. So I delete the formula and hit recalculate (even though I know the formula couldn't have made that error), but the circular reference error stays.

Has anyone experienced this? Is this a known issue for TM1 9.4 and Excel 2010 users?

I suspect this isn't any sort of TM1 issue, but just that Excel has got a bit confused with the dependency tree in your workbook which would otherwise advise the correct order of cells to calculate, one after another, to make sure everything calculated correctly. You could try doing Workbook Tree Rebuild with Ctrl + Alt + Shift + F9 keyboard shortcut. I would also try simply restarting Excel.

Here is the formula I added: =DBRA("tm1 server - apex:Nexus COA",$A12,"Account Name")

Seriously, do you expect someone to recognise and suggest some sort of a magic bullet solution to you, just because you posted this formula? If you understand what a circular reference is, then you'd at least think that posting the content of $A12 would be a good idea, wouldn't you?

[2limit2]

COA2.JPG (21.95 KiB) Viewed 9916 times

Cell B12 has the formula, =DBRA("tm1 server - apex:Nexus COA",$A12,"Account Name"), where $A12 is '1000. It returns the attribute, Cash in Bank, which is the Account Name (Alias) for element 1000.

Here's a screenshot of the attributes editor for Nexus COA.

COA.jpg (28.36 KiB) Viewed 9916 times

It looks like the formula is correct as it's returning the desired value. So I am confused about the Circular References: B12 message at the bottom of the first screenshot.

[rmackenzie]

It looks like the formula is correct as it's returning the desired value. So I am confused about the Circular References: B12 message at the bottom of the first screenshot.

The screenshots are useful and demonstrate that there is no obvious error in your formulas. However, did you try restarting your Excel session or doing the full workbook recalculation, as suggested?

[lotsaram]

You haven't provided any evidence that you have an issue at all. In fact from what you have provided it does not look like there is any circular reference. If there is any problem at all then it may be a bug in Excel 2010 giving incorrect warning messages.

I have experienced issues with incorrect circular reference warnings in Excel 14 a lot lately, specifically when opening CSV files (which is ironic as by definition CSVs can contain only values and not formulas). Restarting Excel doesn't clear the spurious error warning for me, but restarting the computer does, go figure.

[Alan Kirk]

If Excel says Circ Error in the status bar then I don't think it's unreasonable for you to assume that there is one, though I suspect that Lotsaram is correct in saying that it's a bogus Excel message.

If there really is a circular error in Excel you should be seeing not only the status bar display but also blue arrows indicating where the the references are or alternatively a black arrow to a grid if the circular reference appears on another sheet. These don't appear in your screenshot. It might be worth going to the Formula tab, Formula Auditing group and seeing whether the Error Checking item is also showing a circular reference. It does appear to be a bogus error message though, and as Robin indicated your first port of call should be a calculation tree rebuild as he described.

[2limit2]

The screenshots are useful and demonstrate that there is no obvious error in your formulas. However, did you try restarting your Excel session or doing the full workbook recalculation, as suggested?

I'm sorry I missed pointing this out, but yes I did try your suggestions of restarting Excel as well as the full workbook recalc. It still gave me the same error message.

For now, I can live with this error I guess. Other than the fact that if there was a true circular reference, I'm afraid users of this workbook will just ignore it thinking it was bogus.

Thank you all

[rmackenzie]

I did try your suggestions of restarting Excel as well as the full workbook recalc. It still gave me the same error message.

Ah bad luck. I see that Stephen Bullen has an add-in on his page called FindCirc that:

Has Excel ever told you that it "Cannot resolve circular references"? You've looked in the status bar and seen a cell reference that Excel thinks contains the circular reference? It obviously doesn't and you've spent the next 2 days trying to find it? If that's you, then you need this file. It contains a routine to locate the circular reference for you, tracing back from a cell you select. It shows the complete circular reference path (across multiple sheets and workbooks) and even colours the cells involved. The zip file contains an add-in and the source code. While this update works in Excel 97, you can get similar functionality by showing the "Circular Reference" toolbar (which only shows when you have disabled Iteration and have a circular reference).

It could be worth a look but he stated its 'updated to support Excel 97' so you wouldn't be surprised if there were issues on a more modern version of Excel.

[jydell]

Hi all

Similar problem with me. This may not be TM1 related, but I have only experienced this in spreadsheets with TM1 installed.

On all TABs of my worksheet (worksheet has multiple tabs with many TM1 / excel formula's) I am getting a excel circular reference warning pointing to a cell (different cell every tab).

In every instance I have investigated the cell I believe there cannot be a true circular reference.

Below is one of the cells excel referenced as circular (note cell C1 has some text in it no formulas)

Code: Select all

=RIGHT(CELL("filename",C1),LEN(CELL("filename",C1))-FIND("]",CELL("filename",C1)))

(copying this formula into a blank spredsheet works fine as expected)

also example below where the formual does not refer elsewhere yet I still get an excel circular reference message refering to this cell

Code: Select all

=SUBNM("CXMD:costc","","Inventory Report","Name")

Other points
1: I have tried Workbook tree rebuild
2: I have tried re-booting my machine
3: I have tried opening the sheet on someone else PC
4: In the majority of cases the Excel circ reference message points to a TM1 Subnm formula .

Code: Select all

=SUBNM("CXMD:costc","","Inventory Report","Name")

5: I have not tried the Stephen Bullen addin
6: I get the Circ reference both logged onto TM1 or not logged on
7: I get the circ reference in excel without the TM1 addin
8: Refreshing individual tabs (shift F9) I do not get circular reference
9: When tracing the formuls on the above I get blue connector lines, not red. (indicating reference is OK?)
using: Xcellerator v 10.1 Excel 2010

I would like to know if anyone else has resolved such a problem.

[jydell]

Hi

I have resolved this for this workbook.

There was "One" cell in the entire workbook that was actually circular (divided by itself). I found this by process of elimination deleting sheets and recalculating until I found the offending sheet, then found the cell.

Unfortunately Excel was not helping with its circular reference warnings as it continually sent me to cells that were not circular. Not sure if this is an excel bug or an issue with TM1 addin (as TM1 specific formulas seems to cause the problem)

To replicate this
1: Create a simple slice into excel tab (ensure it includes a subnm formula)
2: Copy the tab to another tab on same workbook
3: Make an excel calculation. One cell divided by another
4: Change the above calc to divide by itself (hence go circular)
5: Return to the original tab and refresh (F9)

For me upon refreshing Excel gives me a circular reference warning on the first tab pointing to a Subnm formula. (obviously not circular as it does not refer to other cells) Note if I do the same as above but hardcodse all TM1 slice formulas the circular reference feature operates as expected (indicating circula references but only for the offending cell)

In a small example as per above this is easy to isolate and fix. In a larger example this behaviour can be a nightmare to track down the actual circular cell as excel is constantly referring you to cells that are not circular.

I will be interested if anyone else has the issue above (if it is just me, I can track it down to a TM1 patch or Excel patch that is bad)

Hope this helps anyone else anyone else in the same situation.

Regards