Message error in dynamic charts

[leprince2007]

Hello everybody,
Can you help me please in my problem?
I need to create dynamic charts for multiple suppliers,but I faced a problem when there is no data for supplier "DDD" ,an error appears:
1000334.png
please help me to override this message because :
-it appears after every calculation which is very annoying
-Multiple messages appear in my real data because I have a lot of charts in it
see the attachment

[Glenn Kennedy]

Given your Excel version there is no need for array formulae that double calculate...

Change Z24, copied down to:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$3:$A$995)/($B$3:$B$995=$AA$21),ROWS($1:1))),NA())

Change AA24, copied across and down:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($A$3:$A$995)/($B$3:$B$995=$AA$21),ROWS($1:1))),NA())

Then use CF to hide the #N/A errors.

=ISNA(Z24) and select font colour to be the same as the cell fill. If there were datasets associated with the last chart.... I may have messed them up.

[leprince2007]

Thanks for your great formula but I`am here to discuss the message error in my main thread
please help me to solve this problem

[Glenn Kennedy]

Did you actually bother to open the file? Your (inefficient) formula was the cause of the problem. Hopefully, I fixed the problem AND improved its efficiency.

[leprince2007]

Sorry I didn`t understand that the problem came from my formula
You mentioned something about my excel version.In What Excel version this formula can work?
Because I need a formula to run on office 2007 or newer because I send the file to multiple people and I don`t know the version of office in their PCs.

[Glenn Kennedy]

It will work with 2010+. Does it behave as you want on your (2010+) version of Excel? If it does, I will adjust it to work on 2007+. To save time in other posts ALWAYS remember to state what version it needs to work with... if it is not the same as the version listed in your profile. Otherwise we can only assume that it is intended to work on your system.

[leprince2007]

Sorry for not mentioning the office version
Please adjust it to work on office2007+
Please Also add column matching in the formula

[Glenn Kennedy]

??? What do you mean by column matching??

[leprince2007]

I mean column matching=column number :
index(reference,row number,column number)

[leprince2007]

Forget column matching
Please create the formula for office 2007+

[Glenn Kennedy]

Back to array formulae, like this one... but only calculating once:
=IFERROR(INDEX(A:A,SMALL(IF($B$3:$B$995=$AA$21,ROW($A$3:$A$995)),ROWS($1:1))),NA())

You also need a modification to the named range:
DDD Dates
=OFFSET(Sheet1!$Z$24,0,0,COUNTIF(Sheet1!$Z$24:$Z$221,"<>"&NA()))

and DDD_Product A
=OFFSET(Sheet1!$AB$24,0,0,COUNTIF(Sheet1!$Z$24:$Z$221,"<>"&NA()))

The others may need to be modified too to bring them into line. I haven't done that - just the 4th one. You still haven't explained to me which columns you want matching for (incomplete exchange of PMs). So, until you clarify, I I am unable to do anything about this.

[leprince2007]

Well done thanks
But what do you mean by calculating once? And how did you know that? And you mean that my formula calculates multiple times?

[Glenn Kennedy]

Your formula calculated twice. First time to see if the answer was 0. If it was an error, whereupon it returned a blank. If it wasn't an error it calculated the whole thing again to return the answer. The IF ISERROR construction died with Excel 2003. Much better to use IFERROR.

Please explain whatyou meant by column matching. Which columns???

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[leprince2007]

Ok thanks a lot.
I mean match all columns to make the formula more dynamic. I know how to do it. Thanks
My question is solved

[Glenn Kennedy]

Great. You're welcone and thanks for the rep.