07-03-2009, 09:45 AM
|
 |
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,439
|
|
|
Re: Certain field from each sheet as data series for chart
To help simplify this, here's a little function you can add to your sheet to make an easy list of your sheet names:
Code:
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.
__________________
If you've been given good help, use the  icon in that post to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
Linear Mode
