Formula with a named range referencing to external workbook

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,182
Messages
5,640,667
Members
417,160
Latest member
Timon82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top