Line graph from data in multiple worksheets

[Ubbens]

Hi all!

First I'd like to say that I am quite new to Excel, so please bear with me.

I have an excel file where I have several worksheets, which are all the exact same, documenting data points from my company's social media. For instance, worksheet 1 is week 1, worksheet 2 is week 2, worksheet 3 is week 3, etc.

What I want is to create a 'master' worksheet, which would allow a quick peek into the overal progression throughout the weeks. Say for instance that in each worksheet, on B3 I have the amount of likes increase noted down. What I want is to have a line graph on the master worksheet, where you can very easily see how each week the amount of likes has increased or decreased. However, whenever I try to create a graph using the data from the multiple worksheets, rather than the data forming a nice line, all data points show up as separate points on the same vertical Y axis.

As you can see on the below two pictures, I have currently set it up to have one legend entry for each worksheet data point. However, the result of that is the lack of a proper line in the graph.

I hope what I'm trying to achieve is possible, and thanks in advance so much to anyone willing to help me!

 

[pgc01]

Hi
Welcome to the board

The easiest way is to build a table in the master worksheet with the links to the week values.
Then you use that table to build the chart.

 

[ask2tsp]

Hello Ubbens, let me walk you through the steps to implement my solution.

1. Create master sheet
   designate a cell where you can enter the label of the item you wish to plot (let's say C1).
   name this cell master.itemToPlot
   designate a cell where you want a list of data points to begin (let's say A3).
   name this cell master.topleftDataPoints
2. Create template sheet
   Create a list with all the labels as they appear in your data sheets
   Select this list and name this shtLabels
   
   Return to the master sheet, itemToPlot cell and add a data validation ( value must exist in list shtLabels)
3. Add VBA code to master sheet
   Start Visual Basic Editor by pressing ALT+F11
   In the project explorer pane select the sheet labelled (master). in the properties pane (press F4 to see it) type "master" in the 1st entry labelled (name)
   Likewise select template sheet and change (name) to "template".
   Select master sheet, right click and select code page. Paste the following code in this code window

Option Explicit

Sub buildDataPoints()
    Dim sht     As Worksheet
    Dim dpRow   As Long
    Dim itRow   As Long
    Dim itToPlot    As String
     
    dpRow = [master.topleftDataPoints].Row
    itToPlot = [master.itemToPlot]
    
    itRow = 0
    Do
        itRow = itRow + 1
    Loop Until template.Cells(itRow, 1) = itToPlot
    
    For Each sht In Me.Parent.Worksheets
        If Not ((sht Is master) Or (sht Is template)) Then
            master.Cells(dpRow, 1) = sht.Name
            master.Cells(dpRow, 2) = sht.Cells(itRow, 2)
            dpRow = dpRow + 1
        End If
    Next sht
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Target.Address = [master.itemToPlot].Address Then buildDataPoints
End Sub

Good luck.