+ Reply to Thread
Results 1 to 5 of 5

How to pull results from a report where the locations can occasionally change.

  1. #1
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    How to pull results from a report where the locations can occasionally change.

    I need to build a report that looks at the results from a csv file I paste into my report and then graph them, I paste the csv file into my report every fortnight to record the recent data. The problem I have is that the Area's that I am reporting against as seen in the attachment (column A)can occasionally change, for example one fortnight there might be 4 areas and then the next there might be 5 areas, more often than not the number of areas can increase(albeit not that often but often enough to cause me problems), even less often the number of areas can be reduced.

    What I want to do is keep a record of all Areas that have been in the csv files and have the resulting numbers populate under each date that I paste the csv file in. Set up like as seen in the Graph Figures tab(if this is the most suitable option), allowing me to then graph the results for each Area irrespective of wether the area has been there the whole time or is only a new area.

    The attachment I have set up the first 4 tabs as examples of what I would paste into my report, I would only paste them into one tab and then next fortnight or month paste the next report directly over the top of it. The attachment is just an example of 4 different dates data that will be used. The results are to populate in the Graph figures tab.

    Hope someone can help.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: How to pull results from a report where the locations can occasionally change.

    Beach,

    The most efficient thing for you to do is to create a PivotTable and PivotChart, not use VBA. I have two examples for you, the second structures it as you have requested in your "Graph Figures" worksheet. You don't need multiple worksheets for each month. Rather, do what I did and create a table and then use that table to create your PivotTable and PivotChart. See attached.


    lineChart.xlsx

    Let me know if this works.
    BrownBoy

    If happy, mark "SOVLED" & add to "REP"

  3. #3
    Forum Contributor
    Join Date
    03-19-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    101

    Re: How to pull results from a report where the locations can occasionally change.

    Cheers BrownBoy

    Looks like it could be what I'm looking for, time for me to learn PivotTable and PivotChart, will do this and let you know how it works out.

    Will this PivotTable still work if under each site there are multiple KPI's for each date?

    Site A
    - KPI 1
    - KPI 2
    - KPI 3
    Site B
    - KPI 1
    - KPI 2
    - KPI 3

    What I want to be able to do is select a Site and just see their results and corresponding graph.


    Cheers

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,235

    Re: How to pull results from a report where the locations can occasionally change.

    Hi beach if you need formula to do the job, check this out...

    Cheers
    Azumi
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: How to pull results from a report where the locations can occasionally change.

    Beach,

    PivotTables and PivotCharts are a must. Look at the attached. I don't know what KPI's are, but I added to the table, pivotTable, and pivotChart.

    Take a second to look at the Charts. There are "Labels" with a grey background. If you click on them a filter will appear that allows you to view the data you want. PivotTable tables are intimidating at first, but give yourself a week and you will pick them up quick.Just make sure the source data is arranged vertically and there aren't any blanks.


    lineChart (1).xlsx
    Attached Files Attached Files

+ 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. Replies: 0
    Last Post: 02-12-2014, 04:46 PM
  2. Replies: 2
    Last Post: 08-31-2013, 11:22 AM
  3. [SOLVED] Need to sum multiple locations for 1 program, current results yield 0
    By Webbers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2012, 01:33 PM
  4. Split Excel report and save separate Workbooks to respective locations
    By seattle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2010, 03:30 AM
  5. Pull data from a matrix table into a report using 2 report conditions
    By bing in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-03-2008, 11:51 AM

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