Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suppress Chart Axis Labels if Value is Zero

    I have built in a data validation into a spreadsheet with both state and then county. These are dependent lists where, when you select state in the first drop-down box, all of the counties in that state populate into the second validation drop-down box.

    I have hidden data of roughly 200 vendors with each vendor having 2 performance metrics tied to it (# or orders and timeline to complete those orders). I've created a vlookup table that pulls this data from the master data and changes based upon the state and county data validation box choices. This is how that table looks:

    table.jpg


    So, only a handful of the 200 vendors are present in each county. In the above example, there are only 2 vendors in Autauga County in Alabama. The vendors that have blanks are simply just not doing business in that county.

    My end game is to have a column chart appear based upon this table but ONLY show the vendors that actually have numbers present. So, for example, with the above data, I want the column chart to show only Vendor 9 and Vendor 15, with their respective data. I don't want any of the vendors with no data to appear. I know in a typical scenario (without data validation present) you'd just change the data source each time. But, my hope is to not have to change anything each time (except the state and county drop-down boxes) and for the chart to automatically show only those vendors present in that county with their data columns. Because right now, it looks like this:

    charterror.jpg
    (and, remember, my actual data is roughly 200 vendors, so it's even worse looking than this sample)

    Can anyone help? Any ideas?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Filtering the chart data would be the simplest thing, since the chart will default to not plotting hidden rows.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Mar 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want no manual manipulation by the user other than choosing the state and county. Filtering would be required nearly 3000 times for the nearly 3000 counties in the entire United States.

    The issue isn't the plotting...it's the showing of the vendor names that aren't pertinent to that county.

    Perhaps I need a different lookup table to use as the basis of my chart? I don't know.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can automate the filtering but it's hard for us to provide useful code without seeing the layout of the workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Suppose your data shown in post#1 starts in row 1, column [A].
    You could then use this vba code snippet to filter the column [D] data for those with values:
    Code:
    [A1].CurrentRegion.AutoFilter Field:=[D1].Column, Criteria1:=">0"
    zeddy

Tags for this Thread

Posting Permissions

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