Account Names Range Function (FSN.Range.Accounts)
Account Names Range Function (FSN.Range.Accounts)
Description
The Range > Account Names function, also available by typing FSN.Range.Accounts(), returns a list of GL Accounts using the column configured for accounts on the Dropdown Display preferences pane (for example, DisplayNameWithHierarchy, AccountId, Name, etc.).
If you instead need account numbers (the AccountId column) regardless of the Dropdown Display preference, use the Account Numbers Range function (FSN.Range.AccountNumbers).
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 | TRUE |
| Include System Generated | Option to include system generated GL Accounts (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 |
| Posting Only | When TRUE, restricts the result to accounts that are posting accounts | No | FALSE |
Remarks
- The values returned reflect the Account column selected on the Dropdown Display preferences pane. Changing that preference and recalculating will change what this function returns. To always retrieve account numbers regardless of preference, use
FSN.Range.AccountNumbers. - Filters apply to the same underlying GL Accounts list as
FSN.Range.AccountNumbers, so filter values like generated codes (e.g.,"FSN_L3_BNK") and account number ranges work the same way here. Review the Function filter syntax for selecting ranges as needed. - #SPILL! may appear in the array function cell when the resulting array determined by your parameters will not fit in the provided Excel area. Clear the cells the result needs to spill into, or move the formula.
- 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 using the configured display
If the Account Dropdown Display preference is set to DisplayNameWithHierarchy, the formula =FSN.Range.Accounts("FSN_L3_BNK") returns the bank accounts using their hierarchical display name (for example, Cash : Operating : Bank of America 1234), making them easy to read in a worksheet header or dropdown source.
Example 2: Build a horizontal header of posting accounts for a subsidiary
To produce a horizontal list of posting accounts for the subsidiary in cell D5, suitable for placing across a row of column headers, enter:
=FSN.Range.Accounts(,,$D$5,,,,,TRUE,TRUE)
The result spans across cells using the configured display column, and only posting accounts are included.
Example 3: Validate that all expected accounts are present on a balance sheet
This example assumes account values 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:
=FSN.Range.Accounts("FSN_L0_BAL", B16:B205, $D$5)
This includes all Balance Sheet accounts (referencing the Finsyte-generated system account number FSN_L0_BAL) using the configured display column, excluding any already listed in column B for the selected subsidiary. Any value returned indicates an account is missing from the report.
In cell C206, add a check: =IF(B206<>"","Account Check - Missing Accounts","Account Check - Good"). Conditional formatting can highlight the cell green or red as appropriate.