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.

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.

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.

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.

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.

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.