Printable View
Are you saying that you have one DV with a list of range names, and you want to pick one from that list, and for SUMPRODUCT to use that selected name in its formula?
If so, I think you want
=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,INDIRECT(MAListChoice),0))))
That is exactly what I'm aiming for
Does it matter that the dv has a different number of items from the rest of the sumproduct?
I tried * in between too and I get a #Num error
[vba]=SUMPRODUCT((item27=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(ISNUMBER( MATCH(MA,INDIRECT(maListChoice),0))))[/vba] I noticed the file I zipped for you did have problems with names.
I'm revising an older version before the names were lost.
Thanks so much for your patience and persistence Bob.
Mark
It doesn't matter that MAChoiceList is a different size, but MA must be the same size.
So did the formula I gave not work?
ARe you going to post a new workbook?
Attached is a working file... completely
your Indirect did the trick
I had some named range problems, I found the use of offset instead of naming the entire column.
The calculating was taking a very long time with every change.
Reseting the names to an offset fixed everything.
Your code works like a charm.
I moved all the named ranges to a sheet("library")
The sheet("chart") has the pie chart and the sumproduct and the dataval drop down for the date start and stop and the office choice (MA)
The source data evrything is mapping from is on sheet("QCDetail")
just change the office and dates, and the chart updates.
Thank you so so much Bob.:bow: :friends:
Humbly, Mark
Excellent, dynamic range names are a very useful tool.
Good on you mperrah.
If you can see any other fixes to make the macro's run faster let me know.
I am going to making 19 copies of this file for different offices and one master file to compile all the qcDetails and chart everything.
I'm just very happy this is working.
Thank you again Bob.
Mark