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

If you've used [Named Ranges in Google Sheets](https://support.google.com/docs/answer/63175?hl=en&co=GENIE.Platform%3DDesktop), 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):

```plaintext
=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:

```plaintext
'=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:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1689224086973/078bf05f-8db2-4738-bbfe-7303d3990d26.png align="center")

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](https://support.google.com/docs/answer/3093308?hl=en&sjid=17981813745758026002-NA) returns a reference to the cell passed to the formula.
    
* `ROW()`: The [ROW function](https://support.google.com/docs/answer/3093316?hl=en&ref_topic=3105472&sjid=17981813745758026002-NA) returns the row number of a given range.
    
* `ROWS()`: The [ROWS function](https://support.google.com/docs/answer/3093382?hl=en&ref_topic=3105472&sjid=17981813745758026002-NA) returns the number of rows in a given range.
    
* `COLUMN()`: The [COLUMN function](https://support.google.com/docs/answer/3093373?hl=en&ref_topic=3105472&sjid=17981813745758026002-NA) returns the column number of a given cell.
    
* `COLUMNS()`: The [COLUMNS function](https://support.google.com/docs/answer/3093374?hl=en&ref_topic=3105472&sjid=17981813745758026002-NA) 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:

```plaintext
=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:

```plaintext
=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](https://support.google.com/docs/answer/12504534?hl=en) (\``` GETNAMEDRANGEA1NOTATION()` ``) that I created from this sheet into your own.

[Example spreadsheet with named function GETNAMEDRANGEA1NOTATION()](https://docs.google.com/spreadsheets/d/1MBu5Kdf2KbKcHZjgjmacxx56K537K_cPcV8r59lB-mY/edit?usp=sharing)