Offset returns #VALUE! error when linking to external workbooks

[LearnByGoogle]

Hi,

I am using an OFFSET formula to pull every second cell in a column into my new workbook however it displays the #VALUE ! error unless the other workbook stays open.

I receive a file from my global head office which includes engagement survey scores (lets call it Global). This file displays each department across the top of the worksheet and the questions down the side. The results are then displayed in the table however it has the most recent score in cell D7 (example) then it has the previous surveys score in D8, then the next questions current score in D9 then the previous score for that question in D10 (etc.) The question cells are then merged across those 2 rows for the current score and previous score (so C7 and C8 are merged with the question text in that merged cell). There is also similar merging of cells in the headings row with the department names. I have created my own workbook (Lets call it Model) to analyse these scores and have pulled them into my workbook using the OFFSET formula to skip over every second cell (the previous score) and only display the current score. It works fine until I close the Global file and I dont want to have to have them both open to work in the Model file.

I cannot use INDEX MATCH or VLOOKUP because of all the merging of cells in the Global file. How can I pull the scores into the Model file without having to have the Global file open at the same time?

The formula I am using is -
OFFSET('[180710 RANZG overall results Engagement Scan 2018 June (Q2).xlsx]Team overview'!F$7,ROWS(D$8:D8)*2-2,0)

Hopefully this explanation makes sense.

Thanks!