Setup
====
Sheet1 has sheet-specific named range ('Sheet1!MyTab')
equal to say A1:B3 = 2 col, 3 row = 1,One ^ 2,Two ^ 3,Three
Sheet2!A1 references this range e.g. =HLOOKUP(2.5,Sheet1!MyTab, 2)
Sheet2!A2 references this range e.g. =HLOOKUP(2.5,Sheet1!A1:B3, 2)
Sheet2!A1 & A2 cells both display 'Two'

Error
====
Copy Sheet2 to new Spreadsheet
New Worksheet cell with expected
=HLOOKUP(2.5,Sheet1!A1:B3, 2)
is fine.
However, new Worksheet cell with expected
=HLOOKUP(2.5,Sheet1!MyTab, 2)
Now has
=#N/A

What's going wrong, please?