Referencing a named range in a formula

I see that I can easily use a named range in a formula like so - =COUNTA(DPL). That formula counts all non blank cells in range DPL. However, I need the named range to be dynamic.

The formula below returns the value of DPL.


When I drop the VLOOKUP formula into the COUNTA formula (below) I get the value of 1. I see why I get 1, but don't know how to get it to count values in the named range.


I'm attaching the file in case this makes no sense at all.

Dale LoganConsultantAsked:
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

if you want to pass the range name from a Vlookup result to a Counta, you need Indirect()


the result is 40

cheers, teylyn
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
I have set up the validation lists in the second column to changed based on what is selected in the first column. See attached.

zorvek (Kevin Jones)ConsultantCommented:
The validation list formula for "Variety" cell D4 is:


Once entered the formatting can be copied down to the remaining cells in that column.

Dale LoganConsultantAuthor Commented:
Thanks to both of you for the top notch help. Now the only thing I have to worry about is someone changing the brand after they have already selected the product.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.