Dynamic Named Range doesn't return any values - help!

I am trying to create a dynamic named range using a list in Excel, for a drop-down list. Currently the list looks like this:

Oranges
Apples
Bananas
0
0
0
0
and so on

The 0's are because these cells contain formulas to pull these values from another spreadsheet, which returns 0 if it runs out of values to return. A blank would also be acceptable but that's an extra formula to write.

I have created a drop-down list on another tab using this list. I want the drop-down to only show the non-0 values (i.e. Oranges, Apples, Bananas only, no 0's). But if the external sheet to which this formula is linked to changes, so another fruit is added, the named range extends automatically without external intervention. So if Blueberries is added to the external sheet, the drop-down list will automatically include Blueberries underneath Bananas.

Numerous searches of these forums and others provided me with this solution:
OFFSET($A$2,0,0,COUNTA($A:$A)-COUNTIF($A:$A,0)-1,1)
which I put as the "Refers To" for the named range.
Where A is the column the fruits are listed in and A1 contains the header, which I don't want in the list. This is counting the populated cells in the column and subtracting the number containing 0, plus 1 more for the header.

I built the formula in a cell first to check it works. It does - if I highlight the formula and press F9, it shows me {"Oranges";"Apples";"Bananas"} - no 0's - showing that the array formula is returning the right values.

But when I put this same formula into the Name Manager as a new named range, it doesn't return any values. In the Value column of Name Manager, where the other (non-dynamic) named ranges show the lists of values they refer to, I just get {...}. If I use this named range as a drop-down list using Data Validation, no drop-down box appears when I click on the arrow (and Data Validation warns me there is an error).

What on earth am I doing wrong? I can't find any reference to this issue on the web anywhere. Please help! This is driving me nuts. I am a bit of a VBA novice so I'd prefer not to use it, but if needs must I'll give it a go!