Help with an odd chart

**thebballkid** · 03-26-2009, 01:25 PM

I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).

So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.

I probably confused the heck out of you, but if someone is smart enough to answer this, I will admire you forever. Thank you

**Andy Pope** · 03-26-2009, 01:45 PM

Can you create an example workbook. Couple of periods with 5 or six keywords.

Basically you will need to summarize the information from across the sheets some how. Then you can worry about charting it.

**thebballkid** · 03-26-2009, 02:07 PM

Here is a sample file. I want my chart to show me how the amount of clicks behaved over time for each keyword(sport). Hope that helps. Btw, I only want one chart with all sports on it.

**Andy Pope** · 03-27-2009, 06:21 AM

Pivot table/chart is the simplest thing.

You can either build the data in a single sheet and the create pivot table and chart.

Or use the Consolidate option to select all the sheets and then create a pivot. This allows you to keep stuff on separate sheets but the labeling is limited and the file size takes a jump, which is why I have not excluded it in the attached.

For both cases you will see I have altered you existing data layout slightly in order to make the pt function.