Exclude NA Records from Bar Chart

[LadyDi]

I have a table that lists all the salesmen in one region. However, not all of the salesmen have entered orders this year. On a separate tab, I have a horizontal bar chart that displays the average number of days that it takes each salesman to submit an order. Currently, the chart is showing the salesmen that have not entered orders yet - they could enter orders next month, so I don't want to remove them from the spreadsheet completely. Is there a way to set the chart up to automatically display only the records in the table that show an actual number (instead of NA). That way, even though a salesman did not enter an order in February, if he enters an order in March he will appear on the chart. Is that possible?

 

[noboffinme]

Hi LadyDi

This article might help you, it applies to line graphs but the point is the chart can be made to ignore plotting '0' values.

HTH.

http://support.microsoft.com/kb/302672

 

[LadyDi]

I tried doing as the article said, but all it did was take the NA values out of the chart. It left the names in.

 

[Kiwiman]

Howzit

What version of Excel are you using? In Excel 2010 there is an option to not show data in hidden rows and columns (Rt Click in Chart >> Select Data >> Hidden and Empty Cells.

An option is to hide the rows that show NA - if it is relevant to your version of course. YOu can use vba to do this.

 

[LadyDi]

I have Excel 2007, and do not have the option to hide the rows with NA. If I hide the rows with NA, then they do disappear off the chart. You mentioned using VBA to do this. How would you do that? Can VBA hide and unhide cells based on the total column automatically?

 

[Kiwiman]

Howzit

As an example you can do this. The code is on the sheet_activation. Just navigate to one of the other sheets then back. I had to save as a .xls file to upload it for some reason. Please save as a 2007 format

 

[LadyDi]

That works perfectly. Thank you very much for your assistance.

 

[Kiwiman]

Howzit

I've been meaning to work out how to automatically update a charts data source (in this case, when additional Salesman are added), so I thought I would use the example I uploaded earlier as a starter. This is what I have come up with - you may or may not find it useful.

You can test it by adding \ deleting salesman etc to the Order Detail sheet

 

[LadyDi]

Thank you for the information. I appreciate it.

I do have one more question for you. Is there a way to make this code run at the click of a button, instead of when the sheet is activated? The spreadsheet that I have gets sent to sales VPs, and I'm not sure they will appreciate it when the code runs each time they click on the data sheet.

 

[Kiwiman]

Howzit

Yes - see attached

 

[LadyDi]

That's perfect. Thank you so much for your help.