Not Showing series in chart

  • Hi,

    I am using an interactive chart, as shown in the enclosed file. I want to be able to choose from one variable to five picking from the boxes in the graphsheet. How can i make sure the graph does not show the series that return empty or #NA



  • Hi Jack,

    The only way to make a series disappear is to hide the cells holding the data.

    I have modified your workbook in the following way.

    The chartable data has been rotated so that each series is a column ( makes it easier to hide).

    The blank entry in the variable list is now labelled --+ NOT USED +--. This is simply to make it clearer to the user.

    There is a macro associated with each droplist. The macros check to see if the selected variable is the last in the list. If so the related column of data is hidden. If NOT the related column is visible.

    Hopeful this address your problem, if not post back.

  • Andy

    I am confused with the chart picking up the order of data arrangement from your Drop Down Boxes on the chart. And your restriction of chart for only 5 data series. How are you able to restrict the series and how is the selected data getting picked up for the chart?

    Thanks Andy for your explanation. You are really a Wizard on Charts!! :cheers:

  • Hi,

    The restriction of 5 Data series is not mine but the original posters.

    All five data series were added to the chart to begin with.

    Using the the vba code to hide a column results in a series being removed from the chart.

    If you then look at the chart it only displays valid data series. This is Excels functionallity and not anything to do with my code.

    Change the drop down list and a data series will re-appear in the chart.

    There is one possible exception.
    If you have the option 'Plot visible cells only' unchecked then the data series will remain in the chart.

    Hopefully I have understood and answered your question. If not ask again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!