Let’s say I have a file “AAA.xls”, and within that file is a range “Names” (a vlookup table). I need to reference this range often. Is there any way to set Excel such that I can always reference “Names” without either copying the range to my current document, or having AAA.xls open?
To refer to a named range in another workbook, refer to the range as
‘AAA.xlsx’!Names
such as
=VLOOKUP(A1,‘AAA.xlsx’!Names,2,FALSE)
The path is relative to the referencing book. No need to have the referenced workbook open.
Thanks, that is helpful. But I’m also wondering if there’s a way to define that range with a static name in a way where I don’t need to reference the workbook it’s in every time.
Yes, you can define a named range that refers to a range in the other file. (Sorry, I never did that before so it wasn’t the first thing that came to mind when I read your question.) Go to Formulas, Define Name. If the other file is open, you can click on the formula box and then highlight the range in the other file, or just type in the reference as a formula right into the Refers To box. But the other file doesn’t have to be open to define it, or for it to work.