The OFFSET() function will do that https://support.office.com/en-us/art...e-b4d906d11b66
I'm not sure if the OFFSET() function is limited to open workbooks like the INDIRECT() function is. If you want to be able to close File1, then you will want to test to make sure it works as expected with the closed workbook.

I often use other lookup functions (like VLOOKUP()) for this sort of thing, too.