I would think it is the fact that the named range is dynamic.
You could put a Before_Save macro in the source workbook to define a
static range name equal to address the current range of Tier_Data.

That might well work - but the circumstances that make formulas fail to
work from closed workbooks are far from clear. If you think about it,
Excel has rather a lot to do to compute a VLOOKUP on a closed workbook.
Sometimes I am surprised that it works at all!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup