# Google Sheets - Get A1 Notation for Range of Named Range

## Formula and named function for retrieving the A1 notation for a named range

·

2 min read

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.

## Formula

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:

``````'=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)
``````

## How it Works

So what's happening here and how does this work? Let's assume the following simple spreadsheet:

The cells `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 `A7:C11`.

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.

Example spreadsheet with named function GETNAMEDRANGEA1NOTATION()