• 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.

Scenario:
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!!
Email-Chart.xlsm
0
trixits
Asked:
trixits
  • 2
1 Solution
 
FlysterCommented:
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.

Flyster
Email-Chart.xlsm
0
 
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.
0
 
FlysterCommented:
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!
Email-Chart.xlsm
0

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