Referencing unit codes with mixed cell references and hard-coded values

This topic describes a scenario where a unit code is defined in a cell, and the user wants to reference that cell and use "*,*,*" for the remainder of the unit code values.

Function Arguments for SLGL: AccountCode; Period; FiscalYear; UnitCode; BalanceType

When specifying unit codes in the Excel formulas, to use a cell reference and hard-coded values, you must append the two with "&". For example, if Unit Code 1 value is in cell $A$1, and you want to include "*" for each of the three remaining unit codes, then the unit code parameter would look like $A$1 & ",*,*,*".

In a SLGL formula, that would look like =SLGL(account, period, year, $A$1 & ",*,*,*").

If you want to use a cell reference for Unit Code 2, and you want to include "*" for the remaining three unit codes, it would look like =SLGL(account, period, year, "*," & $A$1 & ",*,*").

For a formula with multiple unit code references to hard-coded cells, you must separate each unit code value by a “,” and then append each value with a "&" again. This would look like =SLGL(account, period, year, $B$17 & "," & $C$17 & "," & $D$17 & "," & $E$17).

If you want to do a range with a starting and ending cell defined, using unit code 3 as an example with starting and ending values in $A$1 and $A$6, respectively, it would look like: =SLGL(account, period, year, "*,*," & $A$1 & ":" & $A$6 & ",*").

Note: This returns all unit codes between and including those values that are in the database, not just those shown on the worksheet.

If unused unit codes exist (there can be up to four unit codes), then using "*" as a placeholder for those unit codes will return all transactions whose unit code equals the specified unit code and the other unit codes may or may not exist. Thus, in the following example, it says to bring back all transactions for Account 4010 with Unit Code 1 = 220 and Unit Codes 2, 3, and 4 can be anything. For example: =SLGL("4010",1,2012,"220,*,*,*","B").

For more examples, see KB 1342616 on the Support Portal.