how to Combine a text + Cell Value that can be used as a name range

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
386
I have a formula that refers to a named range to return total, but the name range after"_" depends cell value. How can combine a text + the cell value that would call the name range. The formula I used below is giving me #value ! error.



=SUMPRODUCT((Company=c2)*(CC=D2)*("Amount_"&E2))

Name Range: Amount_PROC
E2 has a value of PROC
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
How about
=SUMPRODUCT((Company=C2)*(CC=D2)*(INDIRECT("Amount_"&E2)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
You're welcome.
 

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
386
Just a quick query, the INDIRECT works perfectly ok, but when i make the "Amount_PROC" as dynamic using the OFFSET formula (=OFFSET(Sheet2!$K$2,0,0,COUNTA(Sheet2!$K:$K)-1,1)), returns me #Ref! error

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
The Indirect function does not work with dynamic names unfortunately.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Not that I'm aware of.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,041
Messages
5,472,136
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top