I seem to recall that calculated range names which use OFFSET only work when the workbook is open, but I may be wrong.
What I would try to do is the following:
- Convert your range with data to a table
- Now re-create the range name(s) pointing to the relevant parts of the table (instead of the OFFSET, use direct table references, Excel will do so automatically)
- These new range names will auto-expand.
- Use those range names from your other workbook.