INTHEBLACK June 2022 - Magazine - Page 67
03
CLICK HERE TO ACCESS
extra material, a podcast
and a video from
Neale Blackwood CPA
formulas and it works seamlessly with the
LAMBDA function. We will create a function
called SheetName that returns the sheet name
based on a cell reference.
Figure 06 shows all the formulas that
extract the sheet name from the current
sheet. The formulas are displayed below the
cell that contains the formula. The sheet is
called Next example.
The formula in cell A1 works in all versions
of Excel.
The LET function in cell A4 requires the
subscription version of Excel. The LET
function shortens formulas by reducing
duplication by using variables. Note the CELL
function is used three times in the cell A1
formula, but only once in the LET version
in cell A4.
The LAMBDA function in cell A7 provides
the cell reference (Ref) for the CELL function
within the LET function.
This will be a common structure. The
parameter from the LAMBA function is passed
directly to the variable definition in the LET
function, making it easy to convert an existing
LET function into a LAMBDA function.
Cell A10 uses the range name function that
uses the LAMBDA function and returns the
sheet name.
04
05
06
required by the function. You can mimic this
functionality by entering the function details
in the Comment section of the New Name
or Edit Name dialog. You can do this at the
creation stage (Step 2), or you can do it after
you have created the name.
In the Formula tab click the Name Manager.
Find the GSTExclusive and select it and click
the Edit button. In the Edit Name dialog add
the comments shown in Figure 05.
The comment text appears when you start
typing the name.
Notice that in the Refers to box the
reference to cell reference $H$1 (the GST
percentage) has been updated to include
the sheet name, which is GST example. This
ensures the function works consistently
throughout the file.
SHEET NAME FUNCTION
The next example is more complex and
expands upon the April 2021 article that
demonstrated the LET function. The LET
function allows you to use variables within
SHARING FUNCTIONS
If you copy a sheet from one workbook to
another, the LAMBDA range name functions
you have created will also be copied across.
Note the GST example is not a good function
to copy between files, as it relies on a link to a
cell in the file for the GST amount. The second
example will work in any file.
This has been an introduction to the topic of
the new LAMBDA function. This new function
opens up a lot of opportunities to shorten,
centralise and simplify formulas in Excel.
ADVANCED FORMULA ENVIRONMENT ADD-IN
To coincide with the release of the LAMBDA
function, there is a new free Excel Add-in
called the Advanced Formula Environment,
which can make working with LAMBDA
functions easier. It provides a separate
formula editing task pane, which adds line
breaks and syntax colours to LAMBDA range
names. This is like working with DAX measure
formulas in Power Pivot or Power BI.
intheblack.cpaaustralia.com.au June 2022 67