+ Reply to Thread
Results 1 to 13 of 13

Dynamic Charting with Multiple Charts

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Dynamic Charting with Multiple Charts

    Friends - I have been asked to show 9 charts in dynamic form, only showing the last 30 days worth of data. I am teaching myself how to do this, but the examples I've seen have all only been with two points of data - usually a date, and whatever is being tracked. I have 9 charts with a total of 14 points of data, one of which is the date column. I'm wondering if there's an easier way to make all of these charts dynamic at once, or do I have to define 14 names, or more?

    Halp. And happy Friday. Let me know if more info is needed.

    Laura

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    You may have to make 18 names! That is if they are 9, totally independent data sources. You'd need a name for the date and another for the metric being tracked. Of course, I am assuming the worst case.

    First a couple of wikis that may help:
    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

    And if you want some help with this particular set of charts, it would help a lot to attach a sample file.

    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.
    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
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    Hey Dflak - thanks so much for the info. Hopefully the sample sheet is now attached. I pared down the workbook so that it's just the tab with the dynamic charts. Basically, my boss wants to only see the last 30 days for all 9 of these charts. All 9 charts are using the same column for dates, so hopefully that eliminates some named ranges...maybe? He also added columns for Lower control Limit, Average, and Upper Control Limit (maybe there is a better way to do this as well - I haven't looked into it yet).

    Anything you could do to help would be awesome. In the mean time I'll check out those resources you posted. Thank you so much.

    Laura
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    This looks very do-able. I will probably get to it tomorrow.

    One side question: how does the data get entered? Is it manual or copy/paste?

  5. #5
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    You are seriously awesome. Thank you so much. So, the data gets pulled from a different sheet that I deleted for the sake of simplifying the file, and I have a row across the top that pulls in the values from certain cells on a specific sheet. Then I just copy those and paste them as values for the day. Not very sophisticated but it works well enough.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    Here it is. I am using the techniques mentioned in the links in my previous post.

    I converted the information on the Daily Charts sheet to an Excel Table. Excel Tables automatically copy down formulas and they know how big they are and they are addressable by column title name. When you copy/paste data to the end of the table, the table expands to include the data so formulas, pivot tables and charts built off the table still reference the correct amount of rows.

    The offset calculation sheet is used to calculate some of the key numbers needed to make the named ranges.

    Cell B1 looks at the date and gets the maximum date: =MAX(Table_Data[Date])

    Cell B2 finds the row, relative to the table, where this date is found: =MATCH(B1,Table_Data[Date],0)

    Cell B3 backs off 29 days (you want 30 and we already include the last day): =B2-29

    Next I define a range for the date to plot. Plot_Date =OFFSET(Table_Data[[#Headers],[Date]],'Offset Calculations'!$B$3,0,30,1)

    The Table_Data[[#Headers],[Date]] happened when I selected that cell as the start point. Normally tables are helpful in formulas. In this case, it actually makes things less clear. This reference is Cell B5 on the Daily Charts sheet.

    So, the formula could just as well been written =OFFSET('Daily Charts'!$B$5,'Offset Calculations'!$B$3,0,30,1)

    The basic offset formula has 5 arguments: start point, rows to go down, columns to go right, rows to return and columns to return. So
    We start in Cell B5,
    Go down the number of rows calculated bu cell B3 on the Offset Calculations Sheet,
    Go right zero columns,
    and return a range 30 rows deep and one column wide

    With the current data this is the range B35:B64 (Jul-13 to Aug-23).

    Once a range is defined, you can define other ranges from it. For example Plot_LCL =OFFSET(Plot_Date,0,6). This means Plot_LCL is the same range as Plot_Date, but shifted down zero rows and right 6 columns.

    The named ranges shown on the Offset Calculations sheet are just for show. The actual ranges are defined with the name manager.

    These named ranges are used in plotting the charts. For example, all the X-Axis were entered as ='Daily Charts'!Plot_Date. If you go back and check them you will see the workbook name!Plot_Date. That's because the named range is global in scope.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    This. Is. AMAZING. One quick question - the table features columns F1, F2 and F3 - what are these for?

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    Table headers have to be something. These are "filler" headers - make them the same color as the background.

  9. #9
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    Got it! So just spacers basically?

  10. #10
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    One other question, and maybe I should ask this somewhere else, but - my charts still show dates outside of the date range on the X axis, even though the axis bounds are set to "Auto" as far as I can tell. Is this something I'll just have to live with?

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    You could change the chart types to line charts instead of X-Y scatter plots (I've found they do not always scale well), they will look just the same and the trend lines will still work. Then you could set the Axis Type to Text.

  12. #12
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Dynamic Charting with Multiple Charts

    Dflak, you are seriously a lifesaver. I don't think I would have ever been able to figure this all out on my own. Thank you so much!!!!

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: Dynamic Charting with Multiple Charts

    Read the references I posted and you will be able to do it on your own .

+ 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