Explanation
The goal is to return the full address of a range ornamed rangeas text.
The purpose of this formula is informational, not functional.
This version can also report the range returned byanother function(like OFFSET).
Note: theCELL functionis another way to get the address for a cell.
In addition, CELL is avolatile functionand can cause performance problems in large or complex worksheets.
For those reasons, I have avoided it in this example.
See below for more information about the dynamic array version.
The rest of the problem is the details of collecting the coordinates needed by the ADDRESS function.
ROW and COLUMN functions
TheROWandCOLUMNfunctions simply return coordinates.
ROWS and COLUMNS functions
The ROWS and COLUMNS functions return counts.
For example:
Note that ADDRESS returns an address as anabsolute referenceby default.
In this case however, we are giving ROW and COLUMN the rangedata, not a single cell.
This is where the formula gets a bit complicated due to Excel version differences.
This works fine for the problem at hand, because we only want the first value.
Note that the implicit intersection operator (@) isnot requiredin Legacy Excel.
However, it is requiredin the current version of Excel.
Returning to the formula, ROW and COLUMN return their results directly to ADDRESS as therow_numandcolumn_numarguments.
Withabs_numprovided as 4 (relative), ADDRESS returns the text “B5”.
ADDRESS returns “E16”, the address of the last cell indataas text.
At this point, we have the address for the first cell and the address for the last cell.
These values are assigned with the TAKE function like this:
TheTAKE functionreturns asubsetof a given array or range.
The size of the array returned is determined by separaterowsandcolumnsarguments.
Negative numbers take values from the end or bottom of the array.
However, the reference is there, as is obvious in the next step.
“range”, “data”, etc.
This is done with theISREF functionin the first step.
If the value passed in forrangeis a valid reference, we use it as-is to assign a value torng.
If not, we run it through theINDIRECT functionto try and get a valid reference.
The custom GetRange function makes it possible to print the range returned by a formula explicitly.
Other ideas
I looked at two other formulas to solve this problem.
The first formula then uses the TAKE function to get the first and last address.
On small ranges, these formulas should work nicely.
The proposed formula solution above avoids this problem by only creating the first and last reference.
The tradeoff is that the formula is more complex.
For example, =ADDRESS(1,1) returns $A$1.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
COLUMN Function
The Excel COLUMN function returns the column number for a reference.
For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.
When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
ROWS Function
The Excel ROWS function returns the count of rows in a given reference.
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference.
LET Function
The Excel LET function lets you define named variables in a formula.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
TOCOL Function
The Excel TOCOL function transforms an array into a single column.
By default, TOCOL will scan values by row, but TOCOL can also scan values by column.
Related videos
How to create a named range
How to create an Excel Table
