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

[Beach Walker]

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.

[BrownBoy]

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.

[Beach Walker]

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

[azumi]

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

Cheers
Azumi

[BrownBoy]

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