A lambda to simplify the process of deriving the address of a range for the purposes of navigation (use in hyperlinks) and documentation and checklists..

Development Rationale

Excel comes with both the ADDRESS function and the CELL("address") options for retrieving or calculating the address of a single cell.  Retrieving the address of a multi-cell range can become a little cumbersome if done from first principals.  The address of a range is a fairly simple thing to retrieve in a VBA function, so an alternative is to use a custom function written in VBA.  But this lambda insulates the user from the steps required, and doesn't require any VBA, making it possible to report out the full range address of any range in a workbook.

About the Lambda Calculations

This lambda also provides optional arguments to return the address including the worksheet name where the range resides and optionally to return the address as an R1C1 address.  For readability, A1 addresses are returned in relative format (e.g. A1:B20), whereas R1C1 addresses are returned in absolute format (e.g. R1C1:R20C2).

1   =LAMBDA(target_range,[with_sheet],[r1c1],
2   LET(topLeft,INDEX(target_range,1,1),
3   botRight,INDEX(target_range,ROWS(target_range),COLUMNS(target_range)),
4   cellCount,ROWS(target_range)*COLUMNS(target_range),
5   sheetName,IF(with_sheet,IFERROR(TEXTAFTER(CELL("filename",topLeft),"]",-1),""),""),
6   useAbs,IF(r1c1,1,4),
7   useA1,IF(r1c1,0,1),
8   topLeftSheet,ADDRESS(ROW(topLeft),COLUMN(topLeft),useAbs,useA1,sheetName),
9   topLeftCell,ADDRESS(ROW(topLeft),COLUMN(topLeft),useAbs,useA1),
10 botRightCell,IF(cellCount>1,":"&ADDRESS(ROW(botRight),COLUMN(botRight),useAbs,useA1),""),
11 IF(with_sheet,topLeftSheet,topLeftCell)&botRightCell))

In line 1 above, the lambda is defined, with three arguments, the second and third of which are optional: with_sheet and r1c1.  Just as in the notation in Excel, the brackets indicate an argument is optional and the argument may be omitted when the functions is called.

In line 2, the LET function in the fourth argument of the LAMBDA encases the step-by-step calculation of the required result.  Initially, a local value called topLeft is derived, referencing the topmost, leftmost cell in the referenced range.

In line 3, the local value botRight is derived, referencing the bottom-most, rightmost cell in the referenced range.  Note that this may be the same cell referenced by topLeft, a thing that is tested for in row 10.  If the cells are the same, that is the area referenced is a single cell, only one cell's address should be returned. If the range referenced contains multiple areas, the range address returned will be for the first area only.

In line 4, the local value cellCount is derived being the count of how many cells are referenced, the product of rows x columns.

In line 5, the local value sheetName is derived.  If the argument with_sheet is TRUE, then it is the name of the worksheet where the range resides, otherwise it is an empty string.

In line 6, the local value useAbs id derived, that determines the absoluteness to be used by the ADDRESS functions to return the addresses.  It assigned the value 1 (for absolute addresses) if the address is to be returned in R1C1 format and it assigns the value 4 (relative address) if the format to be returned is A1.  This is to make the addresses more readable.

In line 7, the local value useA1 is derived, that determines the reference format to be used by the ADDRESS function to return the addresses.  It is assigned the value 0 (for R1C1) if the address is to be returned in R1C1 format and it assigns the value 1 if the address is to be returned in A1 format.

In line 8, the local value topLeftSheet is derived, being the address of the topLeft cell in the address format selected with the sheet name where it resides.

In line 9, the local value topLeftCell is derived, being the address of the topLeft cell in the address format selected without the sheet name

In line 10, the local value botRightCell is derived, being the address of the botRight cell in the address format selected with a leading colon added.  This value is generated if there are more than one cells in the range (cellCount>1) otherwise it is an empty string.

Finally, in line 11, the return value of the LET function, which is the result returned by the LAMBDA.  It is the result of concatenating  either topLeftSheet (if with_sheet is TRUE) or topLeftCell with botRightCell.

Defined Name Settings

To create the lambda, simply use the define name¹ feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λAddressOf

=λAddressOf(target_range,[with_sheet],[r1c1])

where:
   target_range is a reference to a range in a worksheet within the workbook whose address is to be derived.the range may be a single cell but may not be a multiple-area range.  If a multi-area range is passed the function returns the #REF! error.

   with_sheet is an optional argument that specifies whether the returned address should include the worksheet name of the sheet where the range resides.  It is expected to be a logical value TRUE or FALSE.  If omitted, FALSE is the default value.  If a number is passed, 0 is coerced to FALSE, all other values coerce to TRUE.  Text strings will cause the function to return a #VALUE! error.

   r1c1 is an optional argument that specifies whether the returned address should be in r1c1 address format.  It is expected to be a logical value TRUE or FALSE.  If omitted, FALSE is the default value.  If a number is passed, 0 is coerced to FALSE, all other values coerce to TRUE.  Text strings will cause the function to return a #VALUE! error.

 Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

Version History

¹  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

² You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.