+ Reply to Thread
Results 1 to 12 of 12

plot empty cells as interpolated

  1. #1
    Registered User
    Join Date
    06-25-2005
    Posts
    46

    plot empty cells as interpolated

    Hi,

    I am trying to use the above setting in Excel 2003, but it is greyed out. Nothing I have tried will make it work - must be some setting or something that stops it working...

    Any ideas?!

    Thanks a lot,


    Roly

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    Hi,

    I am trying to use the above setting in Excel 2003, but it is greyed out. Nothing I have tried will make it work - must be some setting or something that stops it working...

    Any ideas?!

    Thanks a lot,


    Roly
    Hi,

    try a chart type change to a type that permits Interpolated, it shows as grayed out for chart types such as Pie etc.

    hth
    ---,
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    Hi Bryan,

    I have got this to work, but I still need a column chart. What I am trying to do is run a monthly report that is shown in a column chart. I want the chart to take its information from a list of customers and if there were no sales that month I do not want to show the customer on the graph at all. I don’t want to change the data range every month, just throw in the data and update all the reports.

    I was looking yesterday and tried #N/A, but the column or gap still appears...

    Do you know of a way to do this?

    Thanks for your help,


    Roly

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    Hi Bryan,

    I have got this to work, but I still need a column chart. What I am trying to do is run a monthly report that is shown in a column chart. I want the chart to take its information from a list of customers and if there were no sales that month I do not want to show the customer on the graph at all. I don’t want to change the data range every month, just throw in the data and update all the reports.

    I was looking yesterday and tried #N/A, but the column or gap still appears...

    Do you know of a way to do this?

    Thanks for your help,


    Roly
    Interpolated is a way to stop a line (on a line chart) from dropping to zero and returning. It has little use in a Bar chart.

    If you have no sales for the month just put a zero in the column, this will leave a gap in the chart where the month should be.

    If, for some reason, you do not want to display gaps then you can Hide the related column or row, either manually or by some Worksheet_Change type event.

    note: Not displaying the gap will present a distorted view of sales.

    hth
    ---

  5. #5
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    Hi Bryan,

    This report is not actually sales, but service level on those sales. These things you have told me about all work, but not in the way I want (useful to learn about though). Where customers don't make any orders in a month, I want them not to show up in the chart at all - no gap or 0.

    I can do this manually every month, but I want all of our customers in the data range, with formulas showing the service level - this is so I can paste a report into another sheet and it updates the graph, with no extra hassle. Any that have no sales should not be in the graph at all.

    Thanks again,


    Roly

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    Hi Bryan,

    This report is not actually sales, but service level on those sales. These things you have told me about all work, but not in the way I want (useful to learn about though). Where customers don't make any orders in a month, I want them not to show up in the chart at all - no gap or 0.

    I can do this manually every month, but I want all of our customers in the data range, with formulas showing the service level - this is so I can paste a report into another sheet and it updates the graph, with no extra hassle. Any that have no sales should not be in the graph at all.

    Thanks again,


    Roly
    You can, as suggested, put in some VB code on a macro, an event, or a button, to Hide those customers that are not required, a simple loop from the first non-required row to the last non-required row (or columns) to hide the row or column if the condition of no activity is met.

    hth
    ---

  7. #7
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    Can you help with the code for that - I have a very basic knowledge of VBA and couldn't write something like that myself?

    Thanks,


    Roly

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    Can you help with the code for that - I have a very basic knowledge of VBA and couldn't write something like that myself?

    Thanks,


    Roly
    there are not many people who can write code for a test of no service (presumed to be zero) in (perhaps) an unspecified column or (alternately) an unspecified row, or an unspecified intersection (cell), to hide succesful candidates by (perhaps) column or (alternately) row.

    Are there any other clues?
    ---

  9. #9
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    Will this help?

    The data at the bottom of the graph is how I want the data to be (it won't be in any order), but companies with zero sales not showing on the graph.

    Thanks for your help,


    Roly
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    Will this help?

    The data at the bottom of the graph is how I want the data to be (it won't be in any order), but companies with zero sales not showing on the graph.

    Thanks for your help,


    Roly
    Hi,

    If you go Tools, Macro, Macros, enter a name, then Ceate
    then copy the code
    Please Login or Register  to view this content.
    then you can Hide or Unhide rows (from 35 onwards) that have zero figures in column B

    hth
    ---

  11. #11
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    That's perfect - I had adjust the code slightly and it works exactly as I want it to.

    Thanks ever so much for your kind help!

    Roly

  12. #12
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by roly
    That's perfect - I had adjust the code slightly and it works exactly as I want it to.

    Thanks ever so much for your kind help!

    Roly
    good to see it works for you, and thanks for the feedback

    note, you can add Shortcut Keys to both macros to make them easier to run, asin CTRL/Shift/H (hide) and CTRL/Shift/U

    also, the Range should be to 65536, not as shown, but it's unlikely that you'll get that far.

    ---
    Last edited by Bryan Hessey; 04-23-2007 at 07:10 PM.

+ 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