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.

=VLOOKUP(C4,ProductList!$E$2:$G$11,3,FALSE)

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.

=COUNTA(VLOOKUP(C4,ProductList!$E$2:$G$11,3,FALSE))

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


2011-CAP-Trial-Data-Collection.xls
Dale LoganConsultantAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

=COUNTA(INDIRECT(VLOOKUP(C4,ProductList!$E$2:$G$11,3,FALSE)))

the result is 40

cheers, teylyn
0
 
zorvek (Kevin Jones)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.

Kevin
2011-CAP-Trial-Data-Collection.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
The validation list formula for "Variety" cell D4 is:

=OFFSET(INDIRECT("'ProductList'!J3"),0,MATCH(C4,INDIRECT("'ProductList'!J2:S2"),0)-1,COUNTA(OFFSET(INDIRECT("'ProductList'!J:J"),0,MATCH(C4,INDIRECT("'ProductList'!J2:S2"),0)-1))-1,1)

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

Kevin
0
 
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.
0
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.