If you've used Named Ranges in Google Sheets, you likely know the power of being able to refer to a range of cells with a static name without having to update the formulas in multiple cells. However, sometimes you may want to get the A1 notation (e.g.
B2:C5) for that named range without looking it up in the menus.
The following formula will provide the A1 notation for a named range where
named_range is the string name of a named range (without quotes):
=ADDRESS( ROW( named_range ), COLUMN( named_range ), 4, TRUE ) &":"& ADDRESS( ROW( named_range )+ ROWS( named_range )-1, COLUMN( named_range )+COLUMNS( named_range )-1, 4, TRUE )
Or, in one line:
How it Works
So what's happening here and how does this work? Let's assume the following simple spreadsheet:
A6:C10 are defined as a named range called TheGang. For the semi-complex formula shown above, I've created a Named Function called
GETNAMEDRANGEA1NOTATION() and passed in the named range as a parameter (see
B4 in the screenshot above). That function returns the A1 notation for the named range TheGang, which is
Going back to the formula, let's break down what is happening. First, let's look at the components of the formula:
ADDRESS(): The ADDRESS function returns a reference to the cell passed to the formula.
ROW(): The ROW function returns the row number of a given range.
ROWS(): The ROWS function returns the number of rows in a given range.
COLUMN(): The COLUMN function returns the column number of a given cell.
COLUMNS(): The COLUMNS function returns the number of columns in a given range.
Now, let's replace those functions with pseudocode to understand what is happening in the formula:
=GET_A1_NOTATION( NAMED_RANGE_STARTING_ROW, NAMED_RANGE_STARTING_COLUMN ) &":"& GET_A1_NOTATION( (NAMED_RANGE_STARTING_ROW + NAMED_RANGE_ROW_COUNT - 1), (NAMED_RANGE_STARTING_COLUMN + NAMED_RANGE_COLUMN_COUNT - 1 )
To simplify this formula even further, this is what is happening:
=GET_THE_A1_NOTATION_FOR_TOP_LEFT_CELL_IN_NAMED_RANGE + ":" + GET_THE_A1_NOTATION_FOR_BOTTOM_RIGHT_CELL_IN_NAMED_RANGE
Try It Out
You can view my sample spreadsheet at the link below and you can even import the named function (`
GETNAMEDRANGEA1NOTATION()` ) that I created from this sheet into your own.