Search code examples
google-sheetsnamed-ranges

How to get the named range definition in Cell

Suppose I have a named Range in Googlesheets as MyNamedRange with definition as C1:H25

Now In a cell I want to show this value in cell A1 as "C1:H25"

Is it possible through some formula like MyNamedRange.getText or something like same.

Not interested in Google Apps Script to use for this.

I tried using To_Text(NamedRange) but go error.

Solution

  • CELL

    Using the CELL function returns absolute references, $A$1

    =CELL("address", INDEX(rngName,1,1))& ":" & 
 CELL("address", INDEX(rng Name, ROWS(rngName), COLUMNS(rngName))))

    ADDRESS

    Using the ADDRESS function allows you to specify one of four options for its absolute_relative_mode argument, $A$1, A$1, $A1, A1

    =ADDRESS(ROW(INDEX(rngName,1)), COLUMN(INDEX(rngName,,1)),4) &":"& 
 ADDRESS(ROWS(rngName), COLUMNS(rngName),4))