How do I get the address of a range with a formula?

Question

Let's say I have a range called WidgetTypes in cells C5:M5.

I want my formula in cell A1 to return C5:M5 or $C$5:$M$5.

What formula goes into cell A1?

cell("address",WidgetTypes) returns $C$5 but I'm stuck there. I can't get the whole thing.

EDIT

I tried:

function getRangeAddress(range) {
  return range.getA1Notation();
}

and I passed in my named range, like this:

=getRangeAddress(WidgetTypes)

but I got the error:

TypeError: Cannot find function getA1Notation in object Kitchen,Entry,Living Room,Dining Room,Breakfast Room,Master Bedroom,Bedroom,Full Bath,Half Bath,Utility Room,Garage. (line 2).

Answer 1

There isn't a built-in function that returns the address of a named range. One alternative is to use a custom function.

The custom function should parse the cell formula to get the name of the named range then the code code could use that string to get the related range and after that you could use getA1Notation.

Related

• How to pass a range into a custom function in Google Spreadsheets? (see this answer )

References

• Extending Google Sheets
• Custom functions

Answer 2

I added the custom function:

function getRangeAddress(namedRange) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(namedRange);  
  return range.getA1Notation();
}

and to use it, I did

=getRangeAddress(WidgetTypes)

and it returns C5:M5 as a string.

Answer 3

In order to return the range $C$5:$M$5 you can use the following formula:

=ADDRESS(5,3)&":"&ADDRESS(5,13)  

Make a note that when using the function ADRRESS we refer to cell eg.C5 but we first write the row number 5 and then the column number3.

You cannot use the function CELL to get the range of your Named range WidgetTypes.

Why do I get those results?
The syntax for CELL is: CELL(info_type, reference).
In your case, when giving as reference your named range WidgetTypes (which actually is C5:M5) and asking for info_type the "address", the formula correctly "returns an absolute reference as plain text of the top left cell in reference" which is C5

You can read about the type of information you are allowed to use as well as what results you can expect from CELL in the Official Google Documentation page.