+ Reply to Thread
Results 1 to 5 of 5

HELP Macro for Line Chart with Auto updating Information

  1. #1
    Registered User
    Join Date
    06-19-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    3

    HELP Macro for Line Chart with Auto updating Information

    So to put this in layman's terms:

    I am trying to make a line chart that only takes the data calculated from formulas. (See attachments)
    While ignoring the (lack of data) in other areas due to the formula defaulting to no data.
    Currently I have the formulas defaulting to "#N/A" instead of "0" and even if I had just 0's the data is still tabulated.
    I am trying to make a chart that only takes the data that is there, as I will be entering a lot of data and the formulas will be auto calculating.
    I want the chart to adjust in response to the presence of new data.

    The example I am using has 6 data points for each line, but also counts the missing 9 data points. I want it to just tabulate whatever data is there.
    So if there are only 3 data points then I want the chart to only show 3 data points for each line, the same for 12 data points, etc. etc.

    I've almost no experience using Macros and VBA. So I don't even know where to begin with making a macro if that is the best solution.

    (This image is if I select all the relevant cells to create a chart)

    Graph 1.png

    This second image is if I ONLY select 6)

    Graph 2.png
    Thanks

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: HELP Macro for Line Chart with Auto updating Information

    You don't need VBA for this. You need named dynamic ranges. If you feel bold and want to go it alone, here are a couple of references.
    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

    If you need additional help, you'll have to attach a file.

    Attach a sample workbook. 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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    06-19-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    3

    Re: HELP Macro for Line Chart with Auto updating Information

    dflak,

    Thanks for the quick reply! I've attached a sample workbook.
    Hopefully that clarifies the problem that I'm having.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: HELP Macro for Line Chart with Auto updating Information

    The articles I cited explain in better detail what I did.

    Named ranges are defined using the offset command. The offset command has 5 arguments:
    - A starting cell
    - Number of rows to go down
    - Number of columns to go right.
    - Number of rows to return
    - Number of columns to return.

    So for the X_Axis, =OFFSET(Sheet1!$G$4,0,0,COUNTIF(Sheet1!$I$4:$I$18,">0"),1)

    Means we start in cell G4, go down 0 rows, and right zero columns (so we are still in G4).

    To figure out the number of rows to return is a bit tricky, a lot of times you can use COUNTA if you are adding data as you go along. In your case, you already have data. You just want to count those values that are not zero, So COUNTIF works.

    The Y_AXIS values are:
    Y_Exist =OFFSET(X_Axis,0,5)
    Y_LED =OFFSET(X_Axis,0,6)

    If you have a range already defined, you can define new ranges from them with just three parameters: Old Range. Row Offset, Column Offset. In this case, we are interested in the same range as the X-axis, but over 5 and 6 columns respectively.

    Click on the chart and select, select data and edit the series. Replace the fixed series range with the named dynamic range. I did this with your top chart.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-19-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    3

    Re: HELP Macro for Line Chart with Auto updating Information

    dflak

    I finally figured it all out!
    Thank you, this was immensely helpful and informative.


+ 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. [SOLVED] VBA Code for auto updating information on a sheet with information from another sheet
    By Nitro2481 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-30-2015, 09:35 AM
  2. [SOLVED] VBA Code for auto updating information on a sheet with information from another sheet
    By Nitro2481 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2015, 08:46 AM
  3. Replies: 2
    Last Post: 10-16-2014, 08:57 PM
  4. [SOLVED] Updating Line Chart with Named Range
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2014, 09:48 PM
  5. Help with automatic chart updating/auto updating today function
    By Tux2424 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-26-2012, 04:45 PM
  6. [SOLVED] Auto Updating Spreadsheet with Information in a different order
    By aliciaward1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2012, 04:58 AM
  7. [SOLVED] Populating a chart with Updating information
    By Lost and Looking for Help in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2006, 06:10 PM

Tags for this Thread

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