4

D.xls is a reference worksheet used to define commonly used infomation for a bunch of other worksheets.

D.xls has named ranges for this and that. One of them is Containers

In I.xls I want to use a VLOOKUP on one of D's named ranges.

Can I refer to D's named ranges in a formula? If so, what is the syntax? Names aren't associated with a sheet so the [Workbook]SheetName!explict_range syntax doesn't work.

Searching for help, I can define it as a named range in the destination workbook, but his will require redefining it in every workbook that uses D. I'd rather define it in D and use it where I wish.

Possible?

Additions:

The actual formula is:

=IF(ISBLANK($E2),,VLOOKUP($E2,[D.xlsx]ABS!$E$2:$J$300,5,0))

This works.

Over in D I define Trees to be a named range E2:J300

So, If I am reading your answer correctly, I should be able to change the above to

=IF(ISBLANK($E2),,VLOOKUP($E2,[D.xlsx]!Trees,5,0))

I get the 'Your formula has an error and the sheet reference is highlighted in the formula bar.

Sherwood Botsford
  • 1,889
  • 5
  • 20
  • 35

3 Answers3

7

You simply use: =[workbookName]!namedRange

For example: =D!salesData

Mr. Mascaro
  • 2,693
  • 1
  • 11
  • 18
  • I can't get this to work with the brackets, only with workbookName!namedRange. Can you clarify when those brackets are required? – XOR LX Mar 19 '15 at 18:24
  • Brackets are only required if the Workbook name has a space or is in a different path/not open. – Mr. Mascaro Mar 19 '15 at 18:25
  • I think it is incorrect to use brackets in this case to defined a name referring to a named range defined in another workbook (and with a workbook scope) that contains space(s) in its name, see my answer – hymced Dec 13 '18 at 11:36
4

I'd like to enrich the other answers, since I've found some clarification for the matter.

=WorkbookName!NamedRange

Will work only for public named ranges. Without brackets. If you want to refer a regular range or a named range declared on specific sheet, though:

=[WorkbookName]SheetName!Range

It also seems, like quotes or anything like that to manage spaces may be required only in Excel's cells, that is in UI (and may be locale specific, since on my PC - works without quotes, but at work - quotes are required), while in VBA - you should not bother with them at all. Original info was taken from https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges

Simbiat
  • 339
  • 2
  • 12
3

I use Win 10 fr-fr with Excel 2018 fr-fr and the correct formula to define a name referring to a named range in another open source workbook (with a workbook scope) is:

='Source Workbook.xlsx'!SOURCE_NAMED_RANGE

No brackets used.

hymced
  • 570
  • 5
  • 19