+ Reply to Thread
Results 1 to 15 of 15

Message error in dynamic charts

  1. #1
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Message error in dynamic charts

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

    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.
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

    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

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

    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.

  5. #5
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

    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.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

    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.

  7. #7
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

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

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

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

  9. #9
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

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

  10. #10
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

    Forget column matching
    Please create the formula for office 2007+

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

    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.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

    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?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

    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.
    Last edited by Glenn Kennedy; 11-10-2018 at 09:02 AM.

  14. #14
    Forum Contributor
    Join Date
    02-24-2015
    Location
    Egypt
    MS-Off Ver
    Office 365 ProPlus
    Posts
    532

    Re: Message error in dynamic charts

    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
    Last edited by leprince2007; 11-10-2018 at 02:25 PM.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,984

    Re: Message error in dynamic charts

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1