Thread: Certain field from each sheet as data series for chart

Nothing automatic will make that happen.

You will need code at best, formula with manual entry at worst.

To help simplify this, here's a little function you can add to your sheet to make an easy list of your sheet names:

Public Function SheetName(Index As Long)
    If Index <= Sheets.Count Then
        SheetName = Sheets(Index).Name
    Else
        SheetName = ""
    End If
End Function

How to install the UDF:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The new function is installed and ready to use. It is used like this:

=Sheetname(3)
...gives the sheetname of the third sheet in the book.

To make it so the formula can list all your sheets without you manually changing numbers, in row 1 somewhere enter this:
=Sheetname(ROW())
...then copy that down. When you run out of sheets it will simply return blank cells.

If you decide to start your list in row 5, you need to convert the first value back to "1" to start the list, so that would be:
=Sheetname(ROW()-4)

Make sense so far?

Now, use an INDIRECT() formula to use the list you just created to pull the same cell from the other sheets. Let's say you wanted cell A1 and your list above started in D5, then next to it in E5 put:
=IF(D5 = "", "", INDIRECT("'" & D5 & "'!A1")
...and copy down.

Now you have your data. Highlight it and create your chart.