Excel #ref error: Occurs when data is refreshed

Question

I have made a connection in excel to another excel workbook, using the native functionality found under the data tab in excel. I imported the data into my "Main" excel workbook from the "Other" workbook by Selecting Data->existing connections and then opening the relevant sheet. This allowed me to link the data from my other workbook to my Main workbook.

My issue is when the data in the Other workbook is changed i received a #ref error in my Main workbook. The formulas i use on the imported data are pasted in a separate sheet (i tried pasting it next to the imported data as well). Note: i paste the formulas from row 1 - 500 for the respective column, so that when the data is imported the formula automatically populates the cell with the correct value. When i refresh the data (i.e when data has been changed in the Other workbook) It seems as the formulas i have copied down do not reference the correct cell anymore.

I have tried using an indirect but to no success.

This is what the formula i use looks like

 =IF(INDIRECT("A"&ROW(#REF!))<>"",VLOOKUP(INDIRECT("A"&ROW(#REF!)),'Safex Codes'!$A$1:$H$1000,8,0),"")

However when the data is refreshed "SOME" of the rows in the column the formula is in return this formula

=IF(INDIRECT("A"&ROW(#REF!))<>"",VLOOKUP(INDIRECT("A"&ROW(#REF!)),'Safex Codes'!$A$1:$H$1000,8,0),"")

This occurs everytime i refresh the data. What is strange is that at the end of my data, hence the last time the formula is being called, the referencing to the correct row is completely off.

For example in row 28 i would expect the above formula to reference a28, however i receive

 =IF(INDIRECT("A"&ROW(A24))<>"",VLOOKUP(INDIRECT("A"&ROW(A24))

Answer 1

I'll bet that you're having the same issue I was. When the number of rows in the data range changes, the unused cells previously populated with data are deleted. The formulas that referenced those deleted cells no longer have references to them because they've been deleted (hence the #REF errors).

You can change Excel's behavior with respect to handling new or unused rows using the Properties menu on the Data tab.

In that menu, take a look at the section labeled "If the number of rows in the data range changes upon refresh." I'm guessing you have the first option checked. Try the third option.