CODEWITHSUNDEEP
excelexcel-formulaformulaspreadsheet
sirplus
sirplus

Reputation: 535

In pure Excel, best way to return Named Range Referred To Address (no VBA)

I have a cell which is a named range MyRange. Whats the best way to return the address this refers to into a cell without VBA?

Upvotes: 1

Views: 91

Answers (2)

XOR LX
XOR LX

Reputation: 7742

A variation on teylyn's second:

=CELL("address",MyRange)&":"&CELL("address",INDEX(MyRange,ROWS(MyRange),COLUMNS(MyRange)))

Regards

Upvotes: 1

teylyn
teylyn

Reputation: 35915

For a single cell range you can use

=CELL("address",MyRange)

For a multi-cell range you can use

=CELL("address",MyRange)&":"&CELL("address",OFFSET(INDIRECT(CELL("address",MyRange)),ROWS(MyRange)-1,COLUMNS(MyRange)-1))

Upvotes: 5

Related Questions