If 3000:3999-* will print a separate line for every unique account/department combinatation in the given range, and 3000:3999-` will print a line for each account in the range by summarizing all of the departments in a given account in that range, how can I print all of the accounts for a given range of departments? I need to use the expression in a Smartsheet so that I will be able expand one .Range line into a multitude of lines, one for each department that will summarize all of the revenues or expenses for each department in the account range from 5000:9999. I would like to compare the budgets vs. revenues for the total of all accounts in each department, and then the same for budgets vs. expenses by department. I can do this if I list each department in 1 column but I would like to use the Smartsheet abiltiy of expanding a range of departments in the same way that we expand a range of accounts.

Thanks for your help,
-Phil

Hi Phil,

Just to clarify your question, you were hoping to have the Financial Reporter SmartSheet be able to expand a particular range of accounts so that the detail is one row per department, maintaining the range of accounts. This is not possible with the Expand function as it will always separate rows by account, with or without departments. I can forward this as a suggestion to development.

As you also mentioned, it can be done with the Departmental Comparative SmartSheet, that you can create via the drag-drop method in Adagio Ledger 8.0A. You can have one row per account or range of accounts, and each department in it's own column.

Presumably, you were hoping to expand by departments in order to organize the information in a more readable format. Though I said above that it cannot be done with SmartSheet expand (you can't), there is no reason why you couldn't create a 'static' statement that does exactly this. Put all the individual departments in column A or B or somewhere to the left (say B10 for example). The next column over can have a formula reading the department description with syntax =ADGET("gldept.DESC",B10).

Then all numeric columns either have the account range defined in the formula or refer to another cell (say A3) that contains the range. So for a budget column, you could have either:

=ADGET("gldata.B","5000:9999-"&B10)
=ADGET("gldata.B",$A$3&B10)

I'd suggest creating a row for one department with all column formulas created. Then copy/paste the row for each new department.

If you need a summarized statement, that could appear as a subtotal, a new statement above/below the detailed statement, or on a second sheet or file.