2

In a spreadsheet, I've got a column named range: Sheet1!I:I, named NamedRanged1.

In another sheet, I'd like to get the index of that named range, i.e., 9 (I is 9th column).

I tried with ADDRESS and IMPORTRANGE:

=ADDRESS(IMPORTRANGE("1TBLF4H6r7EpCBtbTaa9Yan986DnyC_7UhDIWAG5gQX8","Sheet1!A1:Z100"))

but without success.

Is there a function which can retrieve coordinates from the name of a named reference?

1
  • Try this formula: ADDRESS(ROW(Book1!NamedRanged1), COLUMN(Book1!NamedRanged1)) & ":" & ADDRESS(ROWS(Book1!NamedRanged1)+(ROW(Book1!NamedRanged1)-1), COLUMNS(Book1!NamedRanged1)+(COLUMN(Book1!NamedRanged1)-1)).
    – harrymc
    Jan 30, 2016 at 18:31

1 Answer 1

1

The current version of Google Sheets doesn't have a built-in function to get the address of a named range from another spreadsheet but you could build a script by using Google Apps Script / .

The following script will add the address of a named range from an external spreadsheet to the active cell

function demo() {
  var url = 'https://docs.google.com/spreadsheets/d/1TBLF4H6r7EpCBtbTaa9Yan986DnyC_7UhDIWAG5gQX8/edit#gid=0';
  var name = 'NamedRange1';
  NAMEDRANGEADDRESS(url,name);
}

function NAMEDRANGEADDRESS(url,name) {
    var ss = SpreadsheetApp.openByUrl(url);
    var rng = ss.getRangeByName(name);
    var address = rng.getA1Notation();
    SpreadsheetApp.getActiveRange().setValue(address);
}

For detailed instructions about how to create your first script checkout https://developers.google.com/apps-script/overview.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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