How to get the named range definition in Cell

Question

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.

I can see how you could use Address with index or offset to get the beginning and end of the named range, but I can't think of a way of finding out if it's absolute or relative - not without comparing two different cells to see if it's changed. — Tom Sharpe
– Tom Sharpe, Commented Apr 25, 2024 at 12:08
same logic should work in sheets as well. Just disregard the @ character within the formula when implementing. — rockinfreakshow
– rockinfreakshow, Commented Apr 25, 2024 at 12:11
Ah so my comment is wrong on all counts. There is no such thing as an absolute or relative reference in a named range in google sheets (unlike Excel). They are entered without $ signs but behave like absolute references. Also no need to use index or offset with Address. I don't mind - I have learnt something. See support.google.com/docs/thread/4380342/… — Tom Sharpe
– Tom Sharpe, Commented Apr 25, 2024 at 16:31

Blindspots · Accepted Answer · 2024-04-25 15:06:17Z

1

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

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

answered Apr 25, 2024 at 14:40

Blindspots

1,0091 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.