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

[fayez_MrExcel]

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

 

[Fluff]

How about
=SUMPRODUCT((Company=C2)*(CC=D2)*(INDIRECT("Amount_"&E2)))

 

[fayez_MrExcel]

How about
=SUMPRODUCT((Company=C2)*(CC=D2)*(INDIRECT("Amount_"&E2)))

Thanks

 

[Fluff]

You're welcome.

 

[fayez_MrExcel]

Thanks

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]

The Indirect function does not work with dynamic names unfortunately.

 

[fayez_MrExcel]

The Indirect function does not work with dynamic names unfortunately.

Ah ok, is there a way to combine a text + cell value to be used as name ranged that is dynamic?

 

[Fluff]

Not that I'm aware of.