Hello,

Just some background in case it is relevant:
In addition to the main financial statements, we have derivative reports. Ex. Income statement lists 5 expense lines, but the derivative reports lists every account that makes up the expense lines. It's an expanded, detailed listing of the income statement.

Now for the issue:
On this detailed listing, the formula to add a column of values is not working. It is adding irrelevant cells and producing a result of "cycle". I will go in and correct the formula and hit save. Here's the quirk: if I change a reporting date, or exit and re-load the report, the formula reverts to the original and does not seem to acknowledge that I made the correction! So I have to do this over again, and again... etc. Time-consuming to say the least.

There are other issues similar to above, ex. I will delete a superfluous line item on this detailed report and it will re-appear if I change dates or exit. Again, I am saving every time.

Any explanation or help would be most appreciated.

JC

Jim:
Your experience sounds odd. Is the detailed sheet a Smartsheet with Ranges? One possible explanation is that you are editing in Expanded mode and then when you expand again, your changes are over-written. Statements should always be edited in Collapsed mode. Let us know if this explanation applies in your case.

Bruce,

I would like to see what's in column A in terms of ranges and smartsheet codes, but I can't seem to reveal that column. All I have is column B and onward; I can't seem to "unhide" column. A.

Jim

Jim,

The reason for a 'Cycle' error is that a sum formula is referring to itself. This of course is not allowed.

Try clicking on the 'button' that is above row 1 and to the left of column A, to select all. Then try Format / Column / Unhide. This should unhide all columns.

If this doesn't work, then you cannot see column A because it has been set to have a column width of 0. In this case, try moving the cursor at the left edge of the cell that displays the column B, and the cursor should turn into a vertical line with arrows pointing to the left and right. Double-click to expand the column, which will resize column A.

I suspect the same as Bruce: That you have a SmartSheet and you are trying to insert a sum on a Generated Row, which is getting deleted when the form is refreshed. If you have toolbar buttons with up and down arrows (to the left of the department selection) and they are active, this verifies that your form is a SmartSheet.

Thanks. Column A was set to a width of zero.

And now I can see that ranges apply to some groups and not others. (I did not create these FS.) I have set all the groups to be ranges. Now, what is the best formula to use to add the column? Ex. if the ".range" is in column D row 51, do I make the formula equal to that cell, or would I go "=SUM(D51..D51)?

If you collapse a form so that '.Range' appears in column A, the numeric columns for standard formulas will contain the total across all the range of accounts. In your example, that would be row 51. If you want to show this same total amount when the form is expanded, it is appropriate to have the simple formula =D51; you don't have to use SUM.

If you want to sum a number of different .Range rows into a single amount, you could use either =D51 + D56 + D62 or something like =SUM(D51, D56, D62). Either work the same way.

Hello,

If I am supposed to make all changes in collapsed mode, how do I edit specific rows that only appear in expand mode? There are some accounts in the range that have zero balances and I do not want to print these. "Zero suppress" does not seem to do anything in either collapsed or expanded mode.

Thanks

The "Zero suppress" function won't change what you see on the screen. But if you Print or Preview, the zero rows should disappear. And remember that all figures in the row must be zero, including previous periods and budget amounts. If that's not working, tell us the version of yoru module (including service pack date).

"Zero Suppress" will suppress zero lines on the screen if it is a .SmartSheet otherwise you must do a "Print Preview".