• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Chart pulling data from various Excel tabs to update with latest values...

Hello Experts!  I need help figuring out the best way to manage a large amount of data.

Goal: To have a chart dashboard on my first tab that updates when subsequent tabs are updated with daily data.

I have an Excel 2007 workbook (Email Chart.xlsm) with several worksheet tabs (Email_1, _2, _3) containing data.  The worksheets are updated daily.  Each worksheet has a different range of data and values (imported from various companies).  

I'd like to have a chart dashboard that references the data from the subsequent tabs that charts:
Daily Revenue by Email
Daily Impressions by Email

And if possible,
Week over Week Comparisons
Day over Day Comparisons

What I have so far is an ALL Revenue table and a pivot chart.  But I'm not sure if the way I populated the table is the most efficient method, since I guess I'll have to rebuild this every month.  Do I have to compile separate tables for each new chart? Or is it possible to pull values directly from the existing tabs?

I am comfortable adding VBA modules if that helps!

May I get an expert's eyes to help me on this?  Thanks in advance for any help!!
  • 2
1 Solution
I hope this gets you headed in the right direction. I added vlookup formulas to the Chart View table which will automatically populate data when the dates in column A change. All you need to do is enter the first of the month in A3, the rest will auto-populate. L2:O3 has the revenue totals by week. Wasn't sure what you were looking for in the Day over Day comparisons.

trixitsAuthor Commented:
Thank you, Flyster!  I think this is the right start... the line graph is perfect for the Rev and Imp tracking.

For Day over Day, I just hoped to see the percent change one day over the next (see Email_1 tab, column K and N).  I'm not sure which graph is the best to use to display this either.

Also, while I have the attention...

How would I (automate) the calculation of 'Day of the Week' averages?

For example, I'd like an on-going graph to show as data is added:
 - Average number of impressions on Mon, Tue, Wed, etc.
 - Average revenue on Mon, Tue, Wed, etc.
Sorry I missed your last post. Here are the figures you asked for. If it's too crowded for you, you can rearrange it to fit your needs. Thanks for the points!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now