# referring to a cell value as part of a named range

#### jkeyes

##### Active Member
I have the following SUMPRODUCT formula where I'd like the sum range to refer to a dynamic named range, i.e. the sum range will be the concatentation of a given cell value plus additional text to refer to a named range of the same name.

For example:

Code:
``=SUMPRODUCT(--(YEAR(LifeCycleMonths)=OFFSET(C12,-2,0)),--(B10&TotalCostOfMedia))``

Cell B10.value = "ABCWidget", and the named range I want the SUMPRODUCT sum range to refer to is: ABCWidgetTotalCostOfMedia

Probably a simple solution, I'm just not seeing it... thanks for any help!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Try the INDIRECT function:

INDIRECT(B10&"TotalCostOfMedia")

Thanks Kristy!

I should have thought of that!

Replies
3
Views
147
Replies
0
Views
186
Replies
4
Views
202
Replies
5
Views
318
Replies
1
Views
475

1,219,812
Messages
6,150,368
Members
450,957
Latest member
Alan361

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back