Solved: Multiple Tab Data to Chart

[hobs31]

I've got a workbook with multiple sheets that I would like to chart specific data from.
Users will be able to add sheets for each new batch of parts (so the charting dataset is dynamically changing). I've discovered that I cannot chart from multiple sheets, so I would like to compile all of the data into one spreadsheet. Here's what's going on . . .

The Summary spreadsheet would look like this:


ColumnA

Batch #
123
124
125



ColumnB

Part Number
456a
457a
456a



ColumnC

Yield %
76.6%
97.6%
86.6%




the data in column A always comes from cell B14, the data in column B always comes from cell B15 and the data into column C always comes from cell C8. Like I said, users can add spreadsheets as needed, but the data will need to be automatically loaded into the summary spreadsheet for charting.

fortunately, I have the charts built and other VB objects, I just haven't been able to figure out the data summary process.
Thanks!

[anandbohra]

pl post your excel file first

[hobs31]

Excel file is attached in a .zip folder. the "summary" page is "yield data". The chart where all of the data is going is "Yield % Tracker (BN)". Thanks for the help!

BTW, this is Excel 2003, don't know if that matters.

[anandbohra]

pl attach excel file itself or send me that zip file in my personal id
bohra_anand@yahoo.co.in
as anytime i download zip files from vbaexpress.com it download attachment.php file & not the actual zip file
i dont know why it happens (might be i am using DAP)

but anyway pl attach excel file or send me zip in my email id i will see & do the needful to my full extent.

[anandbohra]

tried my best to solve your problem.

[hobs31]

Ok, I don't think I've been clear as to what I'm looking for.
Let's say I've got a data table and chart that tracks cell C8 on every new worksheet in a workbook. If I were to add a new worksheet to that workbook, how can I get the data table and chart to automatically look at C8 on my new worksheet?

[JonPeltier]

I'm not going to open a bunch of workbook attachments (nor probably are most people who might help). Judging from your description, you might find the technique here for creating a summary table useful:

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

[Shazam]

Hi hobs31,

Based on your example, See if this method would help.

First you'll need to list your worksheets. Go to the Define name range and input this in the define box.

Name: Sheets

Refer to: =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())

Input formula in cell D3 and copy down.

=INDEX(Sheets,ROWS($D$3:D3))

Then input formula in cell C3 and copy down.

=INDIRECT("'"&D3&"'!C8")


Now for the chart to have it dynamically. Input these formulas in the Define name range as well.

Name: Range

Refer to: ='Yield Data'!$C$3:INDEX('Yield Data'!$C$3:$C$65536,MATCH(2,1/('Yield Data'!$C$3:$C$65536>0)))

Name: Range1

Refer to: ='Yield Data'!$A$3:INDEX('Yield Data'!$A$3:$A$65536,MATCH(2,1/('Yield Data'!$A$3:$A$65536>0)))

I left the example in your workbook below.


Hope it helps!

[anandbohra]

Ok, I don't think I've been clear as to what I'm looking for.
Let's say I've got a data table and chart that tracks cell C8 on every new worksheet in a workbook. If I were to add a new worksheet to that workbook, how can I get the data table and chart to automatically look at C8 on my new worksheet?

hi hobs31

as far as i understood your query is to get the data of B14 i.e. batch no. & C8 i.e. yield in the sheet name yield data if right pl continue
now add n number of sheets & when u finish up the click update yield data button on sheet named "Yield data" this will run a macro to fetch all the data of batch no & yield from the worksheets under current workbook & paste it here.

now with respect to your chart updation dont worry about it as the chart links are dynamic & u dont have to even click button for that

[anandbohra]

Hi hobs31,

Based on your example, See if this method would help.

First you'll need to list your worksheets. Go to the Define name range and input this in the define box.

Name: Sheets

Refer to: =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())

Input formula in cell D3 and copy down.

=INDEX(Sheets,ROWS($D$3:D3))

Then input formula in cell C3 and copy down.

=INDIRECT("'"&D3&"'!C8")



Hi Shazam (http://www.vbaexpress.com/forum/member.php?u=3260)

simply amazing as this is totally new for me.

[hobs31]

:beerchug:
Thanks so much - to all of you!
I was able to use these suggestions, and I pulled a few other things from the forum to make a fantastic spreadsheet. Here's to hoping my comrades like it too!
:chat: