Hello there,

I am trying to retrieve data from a different workbook that matches 2 different criteria with the current sheet and fill the cell. I used array formula for this. The formula is too long as I included the folder path of the other file in it. When I tried the same formula on the excel sheet, its gives error when the source file is not open and work fine when the source file is open.

The array formula in vba gives error. I tried opening the source workbook and then apply array formula using vba, but still shows error.

Please throw some light as to how I can get around with this.

array formula = =IF(ISBLANK(OFFSET('\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$A$1,(MATCH(1,(C8='\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$C:$C)*(B8='\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$G:$G),0))-1,1)),"",OFFSET('\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$A$1,(MATCH(1,(C8='\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$C:$C)*(B8='\\denasvf2b\civil_1\Operational_data_Instruction\Programme\ASE\TEN & 97k\04 XWB 97k Folder\01 - 97k Tooling\01 97k Master Tooling List\Hydro Trackers\[HYDRO 97K TRACKER.xlsx]TRACKER'!$G:$G),0))-1,1))

code I used for array formula

Please Login or Register  to view this content.