I have a QuickBooks client with three locations (classes) and I want to have a side by side comparison. I was able to design the statement but each department is showing the totals for the entire company. The department is shown as a col spec. I can do separate income statements for each department, but I can't seem to get a side by side. I'm new at this. Can you give me some guidance? Thanks

Hi Karen:

Look at the cells where each of the 3 columns intersect with the colspec row (in blue).

What values do those 3 cells contain?

Hi Karen,

Have you had a chance to set up Account Groups in order to drag one to a blank sheet and create a new report? I mention this because there is a built-in option within this process to select a 'Departmental Comparison'. You just have to select the departments you want to compare and the program will put each of them in side-by-side columns and generate the respective formulas and totals.

If you don't use this method but rather are creating the report 'from scratch', then there is a function called RngMerge that you need to read the account from one cell and the department (class) from a different cell.

You may want to use the Account Group method with one of the previously defined groups to see the syntax of the created formulas and use them in the form you are working on.

Yes, I created the comparison using the Account Groups. It gives me a column for each but the numbers for each are the totals for all the locations.

There are two colspec rows, one has the department number and the other has "BAL".

=-GLAMT(D$7,RNGMERGE($B9,STRCAT("-",D$5))) is the formula for the first line of the income statement. The formula for each location has the same formula, but with the last cell reference different (pointing to the department number).

Hi Karen,

Thanks for the information. To interpret the formula and the layout:

Cell D7 will contain the field you want to report on, 'BAL' or the current balance as of the selected period on the toolbar.
Cell B9 contains the account code(s).
Cell D5 contains the department code(s).

The formula will connect all the account(s) and department(s) and sum each of their current balances, and report this (sum) in the cell.

When the report is summarized or 'Collapsed', you will see the range of accounts summed together, rather than the details. If you click the 'down arrow' key on the toolbar to 'Expand' the report (or show the details), then all of the rows that had .Range in column A will generate rows for individual accounts.

Ensure that you have a specific department in each of the cells on row 5. If you see a star *, this means all departments summed together. You can over-type a value in row 5 with a desired department, and then F9 on the keyboard to refresh.

If you selected the 'All departments' option when creating the sheet, you may find that the wrong amounts initially appear. Just press F9 and the amounts will re-calculate correctly.

If you double-click on a cell, you can drill-down to the accounts-departments that sum to the displayed amount in the cell (for most cells that have a GLAMT formula). Perhaps this could be useful to track down what is happening.

When I double click, I see the accounts for that department only, but the totals are still showing up on the statement. Pressing F9 does nothing.
Expanding the statement also does nothing.

Hi Karen,

If you want to back up your FX data and e-mail it to me with the name of the statement you're trying to get working, I'll get the basic column comparison formulas done for you.

To close this thread...

The account ranges on the statement had "-`" as the placeholder for the department. When doing departmental comparative statements, the acount ranges in column B of the SpartSheet should not have "-`" after the account range.