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

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

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()