Hello All,

I have a client using the financial reporter with Ledger 9.0. There is something funny going on with it to be pretty plain about it. When looking at the Consolidated Statement it is giving the correct Revenue total. However, when looking at the totals by department in the expanded view the line totals look good, however the total of the revenue section is the same total of the consolidated statement which is far from agreeing. The formulas seem to be good, Adagio just seems to be pulling extra info into our totals. Anybody familiar with anything like this happening????

Thanks D/C

Hello d/c,

If "the total of the revenue section is the same total of the consolidated statement", how can they not be agreeing? Agreeing to what?

Are you saying that the total of a column is different when the sheet is expanded than when it is collapsed? This is almost always caused by a SUM() function including hidden rows on the expanded sheet. Don't use SUM() on your column totals when a sheet expands and collapses. Use A+B+C+D+E+F..., a formula that can be easily generated with the AutoSum button.

Andrew,

It is not the exact case you are describing, but somewhat similar. Where is the autosum function?? The formulas have been changed, but it still seems to be occurring. What is essentially happening is that every subtotal from the consolidated statement eg. Revenue is the same as the subtotal for Revenue on the departmental statements, which is not the case, but the funny thing is that the individual line totals are correct, they just don;t add up to anywhere near what the subtotals show. I hope that makes sense. Thanks Andrew

D/C

Hello d/c,

OK - one senrtence at a time...

It is not the exact case you are describing, but somewhat similar.

So, the totals at the bottom of a column do not add up to the sum of all the numbers displayed above when a statement is expanded? Check (carefully) for hidden rows being included in the sum when the sheet is expanded.

Where is the autosum function??

On the format toolbar. View | Customize | Toolbars | Reset the format toolbar to make sure the Sigma (Sum) button is displayed. To use the button do the following:

• Collapse the sheet.
• Select the cell to receive the sum.
• Click the Autosum button.
• Ctrl-click or shift-click to select the cells you want to add together.
• Click the Autosum button again.

The formulas have been changed, but it still seems to be occurring.

What still seems to be occurring?

What is essentially happening is that every subtotal from the consolidated statement eg. Revenue is the same as the subtotal for Revenue on the departmental statements, which is not the case, but the funny thing is that the individual line totals are correct, they just don;t add up to anywhere near what the subtotals show.

Sorry, this makes no sense. The first part says the numbers are the same between the statements and "the individual line totals are correct", then they "don't add up". To what?

I hope that makes sense.

Not yet. Sorry.

Andrew,

Sorry for the ramble, trying to make the most sense out of it as possible.

I have found out that the problem is the hidden cell values are being added on. However, my one remaining question is how to make sure that hidden cell does not reappear?

When I switch from collapsed to expanded view, the hidden cell keeps being placed in the formula.



Thanks Andrew

Hello d/c,

Replace the =SUM() formular you have by following the steps under the Autosum button on my earlier answer. For SmartSheets that expand and collapse, you cannot use =SUM() in any area that contains a .Range command. You must explicitly add each cell together (and the autosum button is the easiest way to create the necessary formula).

• Collapse the sheet.
• Click on the cell that will contain the total.
• Click the Autosum button.
• Shift-click or ctrl-click to select the cells above you want included in the total.
• Click the autosum button again.
• Your total will now be right regardless of whether the sheet is expanded or collapsed.

Try it - you'll see.

Thanks Andrew, still one issue on this one however, Adagio is still pulling the subtotals of groups of accounts from the consolidated statement through to the departmental statement.

The individual lines are correct in the departmentalized statement, just have wonky subtotals.

The formulas are chaged, the statement was looking correct, but then it refreshes and Adagio seems to be picking it up the incorrect information.

I am going to send you a copy of this to have a look at when you get a chance as it is a rush situation and we don't want to spin our wheels on this one.

Thanks Andrew

Have you drilled down on the figure and seen what accounts/departments it's picking up?

Andrew,

When the drill down is done on the incorrect figures, it doesn't display anything out of the ordinary, it displays the correct amount.


Thanks,

D/C

I sent a fixed statement to you last night. Isn't it working correctly? You were missing the "-`" for the department on the account ranges.