+ Reply to Thread
Results 1 to 12 of 12

Multiple Dynamic Graphs Issue

  1. #1
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Multiple Dynamic Graphs Issue

    Hello,

    I've been having a bit of a problem lately with a project I've been working on. I need to create multiple graphs base off a data set. I've tried turning the data into a pivot table then creating a pivot cart but I can't get certain metrics to appear properly. Then I tried using the non-pivot format and that with the large amount of data I would be working with would only cause problems when I try to update it for the next week, thus causing it not to be as dynamic as I hoped. I provided a sample of this problem within this post. I also included notes within this spreadsheet for more information. Any help on this would be appericated


    Thanks!
    Attached Files Attached Files

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

    Re: Multiple Dynamic Graphs Issue

    I have a couple of questions. The data comes to you in the format shown on the WSMReport_Test Sheet. Do you copy and paste this data in? Is it manual entry? Is it a constant fixed format meaning always the same number or rows and that each row means the same thing every time? Likewise for the columns: will there always be 5 columns of data or will you be adding a column a week?

    Do you want chart for every account side-by-side or would you prefer a dashboard approach: pick an account and the graph changes.

    Finally can you show us one sample of what a chart might look like? This would really help. Dummy up the data to make the picture if you have to.
    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
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Multiple Dynamic Graphs Issue

    Hello Dflak,

    Thanks for taking the time to take a look at this...

    The answer to your first question is yes the data would be entered in manually, and yes there will be additional data per week
    as this is going to be an ongoing spreadsheet (I've included an example of this enclosed in this post).
    The answer to your other question..is a more dashboard like approach.
    I've enclosed examples of the chart data


    Thanks Again
    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,541

    Re: Multiple Dynamic Graphs Issue

    Now that I see the charts, I have some additional questions. The charts make sense for Facebook, but the two other services don't have spend, not do they have CCNG and I'll need to know some things like what are the Twitter and Instagram equivalents of Impressions or Engagements per Post.

    You are showing 14 charts for Facebook. Some of which cannot be shown for the other two services since the data does not exist. Do you have a different series of charts for these? I can see that only three Facebook charts do not involve spend or CCNG.

    While you are answering these questions I'll get you going on two charts to show you how to set up the rest of them.

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

    Re: Multiple Dynamic Graphs Issue

    The dashboard approach would work if you were always plotting the same things the same way. If everything was always something vs. spend. I could make one chart and be done. You pick the service (Facebook, Twitter or Instagram). You pick the area (ABSYT, CCNGF, VCIT) and you pick the item (Total Fans, Impressions, Engagements, etc.) and the chart would adjust to fit it.

    The key to the enterprise is the offset command. These two articles explain how to make a dynamic named range and how to use them in dynamic charts.

    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

    I got you started with this.

    The OFFSET command has 5 parameters
    - Start Cell
    - Cells to go down
    - Cells to go right
    - Number of rows to return
    - Number of columns to return

    The first name range I defined is:
    Plot_Date =OFFSET(WSMReport_Test!$B$1,0,0,1,COUNTA(WSMReport_Test!$1:$1))

    This means on the WSMReport_Test sheet, go to cell B1, go down zero cells, go right zero cells and give me a range 1 row deep by COUNTA(1:1) columns wide. As you add data, this named range grows to accommodate it.

    Once you establish a range, you can define other ranges as offsets from it. This takes only three parameters. For example one of my ranges is:
    FB_ABSYT_Spend =OFFSET(Plot_Date,11,0)

    This means find the range Plot_Date, go down 11 rows and over zero columns. So in this case itís Plot_Date offset by 11 rows.

    Other named ranges are defined similarly. It helps if you have a good naming convention.

    Here are the ranges I defined so far.
    Plot_Date=OFFSET(WSMReport_Test!$B$1,0,0,1,COUNTA(WSMReport_Test!$1:$1))
    FB_ABSYT_Engagements=OFFSET(Plot_Date,8,0)
    FB_ABSYT_Impressions=OFFSET(Plot_Date,6,0)
    FB_ABSYT_Spend=OFFSET(Plot_Date,11,0)

    I put together two charts to demonstrate the concept.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Multiple Dynamic Graphs Issue

    Hi dflak,

    Great question for Twitter and Instagram it's a little bit simpler. I enclosed a screenshot within this post for both fields.
    In addition, I saw your second post where you provided a smaple of the charts I'll be diving into that more to gain a better understanding,
    but after a quick look I do like the chart setup for this, but I have an another question how would you add an additional week to this? Would the data need to be reselected
    to add in that new week or can the offset formula control that?

    Thanks again
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Multiple Dynamic Graphs Issue

    Also another question since all this data will be connected week over week do you think it would be stragetic to add a method so the dates can be selected like a drop down filter and then by picking the date range the charts will populate accordily?

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

    Re: Multiple Dynamic Graphs Issue

    Yes that is possible. It will take tinkering with the offset command for Plot_Date - the rest of the definitions will fall in line. I'll work this later today.

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

    Re: Multiple Dynamic Graphs Issue

    Here is the workbook with a revised Plot_Date.

    I added a new named range, All_Dates which is simply all the dates. This name is used for data validation.

    I found where the selected dates were located, and computed a new offset for Plot_Date based on these values. Since all the other named ranges are defined in terms of Plot_Date, they got updated automatically.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Multiple Dynamic Graphs Issue

    Hi dflak,

    I took a look at the revised plot date setup and this is brilliant! Another question is it possible to connect this revised plot dates with the other charts like we discussed before?

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

    Re: Multiple Dynamic Graphs Issue

    Yes, it is possible, but you would have to create a named range for everything you want to plot. I've shown you how to do that and the two links I provided will also help.

    If the carts were all of the format spend / metric, then we could make a single chart where you select the service and then the metric you want to plot without declaring a specific named range for each metric.

  12. #12
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Multiple Dynamic Graphs Issue

    Hi dflak,

    I just want to say thank you for your time and effort with this. I was able to use the examples you provided
    and was able to gain a close solution to this problem, but using the naming conventions to create what I needed.

    Thanks Again

+ 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] Graphs Simple issue
    By brandedadnan in forum Excel General
    Replies: 1
    Last Post: 03-08-2015, 05:37 AM
  2. multiple graphs on one screen and moving graphs
    By mufan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-12-2014, 04:49 PM
  3. Dynamic Graphs
    By twalker1228 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-07-2013, 11:45 PM
  4. Dynamic graphs using VBA
    By Big_Kev in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-18-2012, 06:59 AM
  5. DYNAMIC Graphs
    By Fred-MARTIN in forum Excel General
    Replies: 0
    Last Post: 04-15-2011, 01:04 AM
  6. Dynamic Graphs
    By bnl552 in forum Excel General
    Replies: 1
    Last Post: 02-23-2011, 05:16 AM
  7. Automatic Graphs/ Dynamic Graphs
    By DanielWalters6 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-24-2006, 05:35 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