Using Named Ranges in Excel with a selection of cells?

Question

Is it possible in Excel to use a defined named range such as myArray refers to =summary_data!$C$2:$H$6

Then use this name in another sheet to paste the contents of the data to a given range =summary_data!$C$2:$H$6

The reason to do this is that I am injecting data into other sheets in a template and the front sheet takes the data from many sheets.

Currently I can do this with single cells using the following but defining each individual cell is tedious:

=INDIRECT("'" &V1& "'!" & "summary_data_C2") a name is set to summary_data_C2 an V1 contains the worksheet name.

FWIW openpyxl >= 2.4 doesn't care either if you're planning to use your templates with it or add your own: defined names are just constants. — Charlie Clark

Tom Sharpe Tom Sharpe · Accepted Answer · 2016-05-19T10:41:37

Yes, if you highlight the corresponding range in the other sheet and enter

=myArray

as an array formula using CtrlShiftEnter it will set up a link to the original sheet for each cell.