Make D.V. list dynamic even if source list is linked to other cells that are empty

Hello,
Below is a formula which gets placed in the name manager. The object of the formula is to display in a data validation list only the values that exist in the named range and not blanks, this works dynamically

Please Login or Register  to view this content.

Problem i am having is that each cell in the range, which the named range refers to, are actually linked to a different cell in another workbook, every cell in the named range has this dynamic formula:

Please Login or Register  to view this content.

So basically the first formula which is situated in the named range manager will read all the formulated cells even if the formula returns a "", hence the data validation list will go on and on even if there is nothing to display.
Is there any way around this?

=IF([DataBase.xlsx]Sheet3!A2<>"",[DataBase.xlsx]Sheet3!A2,"") could be shorter by:
=[DataBase.xlsx]Sheet3!A2
Sure column A of BrandList is a exact mirror of column A of Database, including blank cell inside.
To get a solid column A from Database, use this formula in A2 sheet BrandList:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Drag down as far as needed.

And the name formula shoud be:

Please Login or Register  to view this content.

Does it work?

If not, try to upload a sample to see what I can do.

Is your data text or numeric? If numeric, try using COUNT() instead of COUNTA().

If that doesnt work, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

If the data is TEXT replace:

COUNTA(BrandList!$A:$A)

With:

COUNTIF(BrandList!$A:$A,"?*")

You're welcome. We appreciate the feedback!