PDA

View Full Version : [SOLVED] Using concatenate to reference a named range



tatendamark
01-24-2018, 03:09 AM
I am trying to get the sumproduct of two named ranges, by referencing their headers (see below), but using the concatenate/"&" function. However, I keep getting a value error.

The range on your left (see below) is called "income1" and the one on your right is called "interestA". There are quite a few of these, from 1 to 10, and A to J.

This works: SUMPRODUCT(income1,InterestA)
But this doesn't work: SUMPRODUCT("income"&B3,"Interest"&F3). Cell B3 is the header "1" of the income range "income1", and cell F3 is the header "A" of the interest range "InterestA".
How do I get the latter to work? Example file attached.



1

A


1041

0.820863


2987

0.065637


6112

0.803949


1587

0.879469


6953

0.040989


5071

0.080362


8413

0.520205


4111

0.639688


4284

0.512224


2333

0.471039


2325

0.28264

offthelip
01-24-2018, 03:59 AM
try this:

=SUMPRODUCT(INDIRECT("income"&B3),INDIRECT("Interest"&F3))

tatendamark
01-25-2018, 02:18 AM
Awesome. It works. Thanx!

Aussiebear
01-25-2018, 02:28 AM
Awesome. It works. Thanx!

So, what stopped you from marking the issue as solved?