Formula with a named range referencing to external workbook

[asad]

Hello All,

I have an array formula that works well if I use it as below:
{=IFERROR(IF(G5>=1,"",SMALL(IF(('S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$C$1:$C$200000=$A$2)*('S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$I$1:$I$200000>3)*('S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$B$1:$B$200000=$D5),'S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$K$1:$K$200000),COUNTIF(D$4:D5,D5))),"")}

So then I created named ranges to make it neater and the named ranges are referencing to exactly the same workbook as in the formula above. The formula above gives me the correct result even with the other workbook closed. The formula with named ranges gives me the correct result but only when the other workbook is open. If the other workbook is closed, it results in an error.

How can I fix this issue?

Thanks

Asad

 

[asad]

Any help would be much appreciated

Thanks Guys

 

[asad]

And just for the information, here is how I have created named ranges in destination workbook referencing to the source workbook.

=OFFSET('S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$C$1,0,0,COUNTA('S:\ERoad Data\Daily Trips from ERoad Data\[ERoad Daily On Time Trips - Express.xlsx]Data'!$C:$C),1)

Is this right, or do I need to change anything?

This works only if the source workbook is open. If the source is closed, the formula returns an error.

Thanks for having a look.