How to use a name defined in an excel file to another excel file

Question

I have defined a name for a data range in an excel file. Now I want to use the same name in different excel file. I tried through various sources but not sure how to do that.

Current Soln: I am copying the data from the source file and re-defining the names for the range. But this seems to be duplicate and creates unnecessary space.

Please let me know a good approach for this.

Answer 1

In a different Excel workbook you would need to specify the workbook name that contains the named range (and its worksheet if the scope for the range is Worksheet rather than Workbook, eg =VLOOKUP(B2,[SO20113631.xlsx]Sheet1!vRange,2,0)) and, should the source workbook not be open at the time, its path also.

An example for a range that might be used as a lookup table is:

where A1:B4 is a Workbook-scoped range named vRange in the workbook on the left and this workbook is open in the same Excel session as 'book2'.

With path the reference might look something like
'D:folder1folder22[SO20113631.xlsx]Sheet1'!vRange if to a local drive (eg D:) where the single inverted commas are critical. You do not need to type all this in however if the reference is added while the source sheet is open since the path will be added automatically when that book is closed. However it will not be updated automatically if the source location is moved while closed.