SumProduct withNamed Range using dropdown list

Hi all,

Been trying to resolve a "hard-coded" range by using Named Range but has been returning "#VALUE!"

By using the below formula; where range J14 to J2608 is hard-coded, it will work.

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*($J$14:$J$2608))

Similarly, by changing range J14 to J2608 directly in the formula bar with the defined Named Range, it will also work.

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*(Plan_Rev))

But by referring the Named Range ("Plan_Rev") from a cell using dropdown list (see below); the whole thing fails.
In my case, cell "C3" will be a dropdown list that user will select the data they wants this table to return; and I am simulating the return of Plan Revenue.

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*($C$3))

Please help!

Cheers,
CL

You would need the INDIRECT() function to translate the cell value into a range reference:

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*(INDIRECT($C$3)))

Originally Posted by JBeaucaire

You would need the INDIRECT() function to translate the cell value into a range reference:

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*(INDIRECT($C$3)))

Dear Jerry,

Using indirect will results in "#REF!". The dropdown list that I am referring to sits in the same worksheet and using data validation to create (which is cell "C3").

The list consists of all the Named Range that I had set up (referring to the data range that I need to use). E.g. col P is "Plan_Rev", col Q is "FCJul". Hence, when I select cell "C3" to be "FCJul", it will refers to the pre-set Named Range for the data; similarly for selection of "Plan_Rev".

Right now, I am only successful in getting the Named Range key directly into the formula bar for it to work; but this method is not dynamic cos I will need to change all formula if I need the other sets of data.

I have attached an example for your reference

Hope I had not confuse you.

Cheers,
CL

Sorry, I didn't realize you were using Dynamic Name ranges...

You cannot INDIRECT() reference a dynamic name range. I have tried long and hard with no success. They're incompatible. You get one or the other.

But your database isn't long enough to actually require it. Just set a Named Range hardcoded and deep enough to cover your expanding needs, a few 100 rows or so?

Once you've done that, you can change your formula in D2 to:

=SUMPRODUCT((Version="Pipeline")*(Rank=B2)*(INDIRECT($D$1)))


Over in H2 I see you were trying the same thing, but you can't reference cell H2 in a formula IN cell H2, that's circular. Put the drop down in H1 and refer to that.

Originally Posted by clng

Please kindly advise a way to circumvent dynamic Named Range problem.

1) You can use a dynamic name range in a cell.
...or
2) You can use an INDIRECT() reference to a non-dynamic range.


Those are your choices. I wasted weeks trying to find a way to get them to play nice together, no dice. So decide what works best for you.

Based on what you've said so far, It sounds like the "report" part of this will remain fixed once you get this sheet fully designed. It's the data part that will keep changing.

So I would keep your dynamic ranges and manually setup all the reporting cells at the top to report the way you want, then leave them. You don't need an INDIRECT() gumming up the process.

But you have to choose. If you want ONE set of reporting cells you can change their meaning by simply clicking a drop down cell, then you'll need INDIRECT(), which means you'll need to hardcode those ranges to depths deep enough to cover all your possible data dumps without being too crazy. Sheet performance might suffer.

So, what's it going to be? Which is more important to you? On this issue, you can't have both in one cell.