+ Reply to Thread
Results 1 to 9 of 9

Automatic Axis Tidying - Interactive Chart

  1. #1
    Registered User
    Join Date
    01-15-2020
    Location
    Hull, England
    MS-Off Ver
    2013
    Posts
    5

    Automatic Axis Tidying - Interactive Chart

    Hi, Hopefully I've successfully attached an example of how far I've got.

    This is formed on an example I found on the 'Net and I've added to in order to make it interactive on both the X and Y axis. I've created a data table, interactive data table with the use of check boxes and an associated basic line chart. The data goes through Jan to June. Currently if I untick Jan then #N/A shows up in the X axis of the chart. What I would like is if I untick Jan, is excludes Jan and the X axis reforms starting with Feb. I thought it might have something to do with the axis type in Axis options, but I've played around in there to no avail.

    This is just an example, but as the spreadsheet is developed, I expect there to be many months of data added, and it is likely that only the latest months will need to be plotted on the chart. I don't want a big gap on the left hand side of the chart as these earlier months are unticked.

    Many thanks in anticipation.

    Ian
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Automatic Axis Tidying - Interactive Chart

    I've added some dynamic ranges for months and areas.
    And change NA() into 0 to help deal with ranges as above.

    check attached file.
    Attached Files Attached Files
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    01-15-2020
    Location
    Hull, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Automatic Axis Tidying - Interactive Chart

    Hi, thanks for your help. Really appreciated. I've never considered dynamic ranges and I've always struggled with OFFSET.

    I get this error though when I untick an area and a month.

    Cheers,

    Ian

    Attachment 657939

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Automatic Axis Tidying - Interactive Chart

    Try to paste picture once more.
    Check attached file, accidentially I've created today something for my friend to display range of months.
    Maybe it gives you a clue.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-15-2020
    Location
    Hull, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Automatic Axis Tidying - Interactive Chart

    Hi KOKOSEK, Hopefully I've been able to add the image this time - the error message says "We found a problem with one or more formula references in this worksheet. Check the cell references, range names, defined names and links to other workbooks in your formulas are all correct." I get this when I untick one of the areas on the left hand side. I've looked at the Name Manager references, but I can'tr seem to work them out. Many thanks for all your time and effort,

    Ian
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    01-15-2020
    Location
    Hull, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Automatic Axis Tidying - Interactive Chart

    Hi all - I haven't been able to get any further with this and I'd be grateful for your help. Revisiting it after a week on another project. Many thanks and happy Friday everyone.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatic Axis Tidying - Interactive Chart

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Cheers
    Andy
    www.andypope.info

  8. #8
    Registered User
    Join Date
    01-15-2020
    Location
    Hull, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Automatic Axis Tidying - Interactive Chart

    Hi Andy, Thanks for your reply - I though I'd attached a file? Thanks,

    Ian

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatic Axis Tidying - Interactive Chart

    Yes with you original problem. Not the one that displays the error in the picture you posted

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-31-2016, 01:23 PM
  2. Interactive calendar and automatic email (Lotus) reminder help
    By Cassie4258 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2016, 10:45 AM
  3. Replies: 1
    Last Post: 11-20-2015, 12:50 PM
  4. chart series data selection - automatic axis update
    By deanf987 in forum Excel General
    Replies: 2
    Last Post: 02-14-2012, 04:34 AM
  5. Automatic Removal of X-Axis Categorical Values in a Bar Chart
    By megahuts in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-22-2010, 04:30 AM
  6. [SOLVED] ehhh, is there a way to automatic size the x-axis of a chart
    By Frodo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-12-2006, 08:35 AM

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