2

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

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

References

  • When you say "that string", do you mean the string WidgetTypes? – toddmo Oct 14 at 19:09
  • @toddmo I'm referring "to the name of the named range", if the name of named range is WidgetTypes, then yes. – Rubén Oct 14 at 19:13
  • Thanks, was just making sure. So then you say use WidgetTypes (the string) to "get the related range" (in order to use getA1Notation. If "the related range" I want to get is C5:M5, are you saying getA1Notation is going to return "C5:M5"? – toddmo Oct 14 at 19:18
  • @toddmo I added a couple of references. Please read them, after that if still have questions, please let me know. – Rubén Oct 14 at 19:21
  • 1
    ok I got it working, see my answer. Thank you! – toddmo Oct 14 at 19:37
1

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.

0

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.

  • I don't have a 13 handy to work with. I just have the range reference WidgetTypes. So how would I get the number 13 (Column M)? That's where I'm stuck, actually. The end of WidgetTypes won't always be M or 13. It may grow or shrink. – toddmo Oct 14 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.