A client wants to print out a statement that includes all departments except those that start with the character 38.

Is there a way under View | Departments to create something that looks like this (in the GLDTREE.DAT file)

1000:37ZZ;3900:99ZZ'All but 38xx

so that when future departments are added, they will automatically be included in the list?

Thanks

Steve

Hi Steve,

If it is just the amounts you are looking for, and not expanding a sheet to break down various departments, you could create formulas for all columns to subtract the 38xx department group amount from the consolidated department amount. Something like this:

= GLAMT("M",4000-*) - GLAMT("M",4000-38??)

Of course you would replace "M" and "4000" with cell references for efficiency, but you get the idea. New departments would automatically be included or excluded in these formula results.
---
There is no automatic consideration for defining department ranges to exclude a particular range of departments in the View Departments area. This part is not possible. You could create a Department List to manually select all departments except the 38xx ones, but new departments would not be automatically included.
---
The Custom Department Range you can define on Statement Group items does appear to support multiple department ranges. If all the department codes are 4 digits, you may be able to define a Custom Range of 1000:3799,3900:9999 that could work for what they are doing, but only for printing Statement Groups. This feature is designed to have new departments automatically included without user intervention.

They want to see the statement either consolidated, or excluding departments 38xx, so hard-coding the formula the way you propose won't work.

What I am going to do is to create a Department Mask "????", called "All but 38xx" (although it is really all departments), and then build this formula into a hidden cell (let's say cell A3):

=IF(TRIM(DEPARTMENT(0))="*",0,1).

The result of this formula will be 0 if the statement is consolidated, 1 if the statement is not consolidated.

Then I am going to duplicate the contents of Column B to Column C, replacing the "-`" in each formula in Column C with "-38??"

Then I can use your formula, revised like this:

= GLAMT("M",$B13) - (GLAMT("M",$C13) * $A$3)

When they select this department group, it will subtract out the 38xx figures.

We will see if this does the trick.

Steve

Good luck! Sometimes these exceptional reports require a little ingenuity.