Auto Add Series in Excel Chart from Data Set

[mttank]

I am wondering if there is a way to have an Excel chart with two or more series automatically detect and name each series from the data set by common text found in one of the data table columns. Pivot tables won't work since they do not allow use of a scatter chart, which best graphically depicts the data. I currently do this by selecting the range for each series but there has to be a better way.

 

[SkipVought]

Hi,

Chart Wizard normally does all that.

Please post a sample of your data and explain exactly how it is plotted.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[mttank]

Hi Skip,
A sample file is attached. This sample has 5 series. I have not had any success in getting chart wizard to auto ID each series (denoted by Project name). I had to add each series individually to create the adjacent chart.
Any guidance on this is much appreciated.
Mike

 

[SkipVought]

Your Projects need to be separate columns.

Make a new sheet, with dates in column A and project in row 1 and the SF as a lookup formula to the original table.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[mttank]

I must be missing something. I'm not seeing how the individual sale dates of units can correctly correspond with the projects when the dates are in a single column and the projects are in a single row but seperate columns. Enlightenment appreciated.
Mike

 

[SkipVought]

Sorry, I had surgery two days ago.

You can use a transform query faq68-5829

Transform SUM(`Sheet1$`.`$/SF`)

SELECT `Sheet1$`.`Orig Date`

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbchart`.`Sheet1$` `Sheet1$`

Group by `Sheet1$`.`Orig Date`

Pivot `Sheet1$`.Project

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[mttank]

Skip,
Hope the surgery went as well as could be expected and that your recovery is on the fast track.

The code worked fantastic with the exception that if two units sell on the exact same day it is returning the sum of the $/SF instead of independent results. Is there a tweak that will allow the dates to be interpreted independently? I suspect it has something to do with the "Group By" and "Sum $/SF" portions of the code. However, my attempts to tweak it have not worked.

Thanks,
Mike

 

[SkipVought]

actually, here's one with lots...
[tt]
Dte $/SF Project
6/1/2006 $414 ISLAND TOWNHOMES
6/1/2006 $354 ISLAND TOWNHOMES
6/1/2006 $315 PARADISE COVE
6/1/2006 $337 PARADISE COVE
6/1/2006 $375 PARADISE COVE
6/1/2006 $294 PARADISE COVE
6/1/2006 $292 PARADISE COVE
6/1/2006 $299 PARADISE COVE
[/tt]
So how would want you plot that? I think that a STACKED COLUMN works. Your way has points almost superimposed on each other. What does THAT graphic tell you? A chart is supposed to transmit a quick impression. A Scatter chart is not really the kind of chart that shows $/SF for a period. But I could be missing your requirement.

I'd suggest also showing another column/series for COUNT, plotted on the secondary axis



Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[mttank]

The reason I prefer a scatter is that you can see each occurance of a sale within the various projects over time to look for pricing changes. I'm not concerned with total volume of sales in dollars and currently track the count (absorption) on a seperate bar chart tied to a pivot table. I know that some points can end up being real close or even on top of each other but it still gives us the best visual for our use.

Thanks,
Mike