I need to create a chart of accounts that only looks at segments 2-5 of a 5 segment chart of accounts. (i.e. ignore segment 1 as this needs to be coming from elsewhere for the structure of this report).
Normally to create a chart of accounts I would have used =GL("Rows","Accounts",,,,,,,,,,,,,,,,,,,,"Company"), but I do not want segment 1 included in the account string. How can I accomplish this?
I am using Jet with Great Plains.
Hi Heather -
I would create a Grouping Report. The top level would list all segment 2 accounts, the next level lists the segment 3 accounts associated with that segment 2, etc.
My sample Fabrikam only has three segments that are used, so I used all three in this example.
Heather -
A faster method would be to list all accounts, but only show the segments you want:
(this example assumes that Seg1 is three digits long... you would want to adjust the parameters of the MID function to accommodate the length of your Seg1)
I tried the first option. It works, which is great! Thank you :)
However, I am using this chart to create a trial balance by company (the first segment is company number, which is why I needed it separate) and it hangs at the 3rd replicator - which is account number for us, so the segment with the most data. Do you know if there is a max data or something that can be pulled through? Below is the report I have created, and it hangs replicating H15.
Hi Heather -
Whenever you have nested functions which refer to outer layers of the nesting (e.g., column H referring to the replicating functions in both F and G), it is best (instead) refer to the the identical values on the same line
I've noted the changes as black lines:
So in cell H15, instead of referencing F13 and G14, reference C15 and D15, respectively.
Does that help?