Account Range Function (FSN.Range.AccountNumbers)
Account Range Function (FSN.Range.AccountNumbers)
Description
The Range > Account function, also available by typing FSN.Range.AccountNumbers(), makes it easy for users to return a list of GL Accounts based on the function arguments.
Function Arguments
| Argument Name | Input Information | Required | Default Value |
|---|---|---|---|
| Include Filter | List out which specific accounts you would like to include in the returned list, understand filter syntax in remarks below | No | |
| Exclude Filter | List out which specific accounts you would like to exclude in the returned list, understand filter syntax in remarks below | No | |
| Subsidiary | Select or type in a valid subsidiary name from the Subsidiary List | No | All Subsidiaries' accounts if omitted |
| Level | Specifies the maximum level of the hierarchy to return. System generated account numbers go down to level three. If you are just trying to return the highest level of account number, type in level 4 | No | |
| Include Children | Include child accounts. When TRUE, all child accounts are also returned. When FALSE, only the parent accounts or accounts without children are returned. | No | FALSE |
| Include System Generated | Option to include system generated GL Account numbers from the GL Accounts List (ex: FSN_L1_AST) | No | FALSE |
| Include Inactive | Checks that IsInactive column in GL Accounts list is set to F (false) | No | FALSE |
| Horizontal | Allows users to adjust the returned list to present across cells rather than down | No | Vertical |
Remarks
- Review the Function filter syntax for selecting ranges as needed.
- The Account Range Function always includes child accounts. For example, if I had a filter on the Include Filter argument of an Account Range function of "1000", account 1000, Cash, and all of it's child accounts (accounts with parents of Cash) will be listed out in your array.
- #SPILL! may appear in the array function cell when the resulting array determined by your parameters will not fit in the provided Excel area. For example, if I have 10 account numbers I want to see returned and I only have room for 5 before there is some value already in a cell, I will get the #SPILL! warning until I make room for my array or move it.
- When specifying a Finsyte-generated account number (Prefix FSN_) in the Include Filter or Exclude Filter arguments, you must use quotation marks around the value. For example, to include all of the Balance Sheet accounts, you would type
"FSN_L0_BAL"in the Include Filter argument.
Example 1: Return accounts with Account Type Bank
Since Finsyte provides account numbers for each account type in the GL Account list, it can be used to pull in the account numbers using the range function. The generated account number for account type Bank is FSN_L3_BNK. In a cell, simply type =FSN.Range.AccountNumbers("FSN_L3_BNK") to return all of the bank accounts.
Example 2: Return only Level 1 System Accounts
The Level parameter works to include only accounts up to and including the specified level. In this example, we only want Level 1 accounts, so we need to explicitly exclude the Level 0 accounts. The function to retrieve only the Level 0 accounts is FSN.Range.AccountNumbers(,,,0,,TRUE). Since all account numbers at levels 0-3 are system generated, TRUE must be passed as the Include System Generated option. This dynamic array will be passed in as the Exclude filter in order to return all of the Level 1 system accounts with the level 0 accounts filtered out. The final formula for returning only the Level 1 accounts is =FSN.Range.AccountNumbers(,FSN.Range.AccountNumbers(,,,0,,TRUE),,1,,TRUE)
Example 3: Determine missing accounts on a balance sheet
This example assumes that the account numbers are in column B based on the standard Balance Sheet Template, and the last row used is 204. The subsidiary dropdown is in D5. In cell B206, enter the following formula: =FSN.Range.AccountNumbers("FSN_L0_BAL",B16:B205, $D$5). Since the balance sheet should contain all of the balance sheet accounts, this includes all balance sheet accounts by referencing the Finsyte-generated system account number FSN_L0_BAL to include all accounts in the Balance Sheet. For the exclude range, it specifies all of the account numbers in column B. Finally, it binds to the subsidiary, since accounts may or may not be available for a particular subsidiary. Once you've written this, try deleting an account number and you'll see that the function returns the value that was deleted.
This formula is useful for adding in a check to worksheets to ensure all the expected accounts are specified. In cell C206, add in an IF statement to look at cell B206 to determine if any accounts were returned and output the appropriate message like this: =IF(B206<>"","Account Check - Missing Accounts","Account Check - Good"). You can further add conditional formatting to format this cell as green or red if you like.