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

Posted on 2012-08-24
Last Modified: 2012-08-29
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!!
Question by:trixits
    LVL 22

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

    LVL 1

    Author Comment

    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.
    LVL 22

    Expert Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now