• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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
0
Dale Logan
Asked:
Dale Logan
  • 2
2 Solutions
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now