Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a simple table of data in Excel that I would like to chart, but I would like to be able to select which columns to chart without having to edit the chart each time. For example I have data like this

    Code:
            A       B       C
    Chart   Y       N       Y
    Data    34      28      42
    I would like my chart to graph only data for A and C, but if I change the datum for B from N to Y then it will automatically be included in the chart.

    Can I do this with some macros, or will I need to fool around in VB? And if VB as there any suggestions?

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Macros are fooling around with VB....

    Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the "N" data to #NA error so that the points will not be plotted...

    How well this work depends on what you have. Could you attach a sample workbook?

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Macros are fooling around with VB....

    Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the "N" data to #NA error so that the points will not be plotted...

    How well this work depends on what you have. Could you attach a sample workbook?

    Steve
    Sure Steve, here it is. The "Usage" sheet is to record what equipment is used each day. Some days may have more than one, some may have zero (but that hasn't happened yet). "Summary" is the summarised data and the graph, and the others are to help calculate stuff for the summary sheet. If anything is not clear let me know. I like mucking about in Excel and have played with VB but I'm sure that I don't do things in the best way.

    I might fool about with that #NA method you suggested. Although I may just try and use VB as I'm sure that this simple workbook will grow over time as I try and add more stuff in.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Did you intend to attach file? There is none attached...

    Steve

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, I thought I had attached it.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    No and you still haven't....

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahhh!! Now I see - I hadn't noticed the "Upload" button!! Here we go.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Craig - Your workbook looks like a good candidate for using Excel Database along with Excel Pivot Tables, Excel Pivot Charts, Excel Data Filters, etc.
    Probably with very little need for Macros/VB.

    On the "Summary" tab, what criteria determines if a Y or N goes in row 4?


    Tim

    PS: The usage of Equipment seems very low.

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your response, Tim.

    The Y or N is changed by me. It doesn't change too often, but some of the equipment that doesn't get used much might get left off the chart sometimes.

    I'm not sure what an Excel DB is, and although I have heard of pivot tables etc I haven't used them much. Do you have any suggestions on how they might be used in this situation?

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Craig - I am working on Database, etc. I noticed that the chart in the example workbook plots LAHF and CP even though they both have an "N" in row 4 on Summary. Is that how it should be plotted?

    Tim

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Tim Sullivan View Post
    Hi Craig - I am working on Database, etc. I noticed that the chart in the example workbook plots LAHF and CP even though they both have an "N" in row 4 on Summary. Is that how it should be plotted?

    Tim
    No, I had just added that row in as an example. I had done things manually before and had never had that row as it had always been in my head. Sorry for the confusion.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The easiest thing may be is to transpose your chart data table so that the Y/N are in a column. Then create the chart with all the transposed data. Add a autofilter to the Y/N column and filter for Y and only the visible data will be plotted...

    Steve

  13. #13
    New Lounger
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Depending on your setup a simple way would be to use an IF to read the Y/N and the data and convert the "N" data to #NA error so that the points will not be plotted...
    Steve, I just tried this and although the points are not plotted, they still appear in the chart. I am using a column chart and there are empty columns for the #N/A data. Thanks for the suggestion, I can imaging that using #N/A may be handy sometimes, but it doesn't seem to help in this case.

  14. #14
    New Lounger
    Join Date
    May 2010
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Two useful tips:

    1. The Y/N is a data mask, remove the N data (replace by null string), and then pack the data to the left to remove the N data.
    2. The ranges you wish to plot are dynamic (width depends on number of Y data) - handle this by using dynamic named ranges.

    In the attached file I've used the SMALL function to achieve task 1. I forget where I first encountered this method, I have found it to be extremely useful as it's often desirable to remove blanks and shift the remaining data to the left (or to the top) so that there are no blank cells in the repacked data.

    Once the data are in contiguous cells, you can set up named ranges, whose width is equal to the number of data points to appear in the plot. The ranges are defined by using the OFFSET range function. Insert the names into the ranges to be plotted.

    The attached workbook shows 1 and 2 in action with your data.

    Hope this helps!

    RAK
    Attached Files Attached Files

Posting Permissions

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