Excel Chart Values

[Kenny100]

I have the following data values in two columns in my Excel spreadsheet:

Location Quantity
Site 001 12
Site 001 21
Site 001 34
Site 002 12
Site 002 11
Site 003 23
Site 003 24
Site 003 43
Site 003 12

These come from a SQL database and are constantly changing.

I'd like to create a bar chart that shows 'Location' on the X axis and 'Quantity' on the Y axis, with the associated total values GROUPED BY LOCATION. However I can't figure out how to make the chart group by location i.e.

Location Quantity
Site 001 67
Site 002 23
Site 003 102

Is there a way of doing this within the chart without separately calculating the total for each location in a separate column?

Thanks for any help!

 

[SkipVought]

Hi,

Sure. You do it with DYNAMIC Named ranges. How can I rename a table as it changes size faq68-1331

Use the OFFSET function in Insert/Name/Define.

Use the...

MATCH function to find the FIRST occurrence of a Location and

COUNTIF function to count how many rows for that Location.

Those two values would be used in the SECOND and FOURTH arguments in the OFFSET function.



Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[Kenny100]

Phew! Thanks for the hard work Skip! Very impressive but any help with putting it all together?! I've read up on what each of the OFFSET, MATCH, and COUNTIF functions do but can't see how I would stick them all together.

Sorry, I'm new to Excel so apologies for sounding thick!

 

[Kenny100]

... and the reason for some of my confusion is that if I use MATCH to find the first occurrence of a Location and then use COUNTIF to count the number of rows for that Location ... how do I continute for the next Location and so on?

 

[SkipVought]

So is this to be a dynamic chart, where the user chooses which series to display or will you display all the series at the same time?

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[Kenny100]

The sheet will query the SQL Server DB every month for updated data (I'm going to create a macro to do this so the user simply clicks a button).

In month 1 the data could be this:

Location Quantity
Site 001 12
Site 001 21
Site 002 12
Site 003 23
Site 003 24

In month 2 the data could be this:

Location Quantity
Site 001 12
Site 001 21
Site 001 34 (new data)
Site 002 12
Site 002 11 (new data)
Site 003 23
Site 003 24
Site 003 43 (new data)
Site 003 12 (new data)

I want my chart to read from these two columns and produce a chart of Location vs. Quantity. Based on month 2's data above, the problem I've been facing is that my chart always shows Site 001 3 times, Site 002 2 times, and Site 003 4 times. I want each site to have a single totalled value on the chart. I'm hoping this can be done automatically within Excel so that I don't have to make modifications each month.

To answer your question, I'll want to display all the series at the same time.

 

[dhulbert]

Keny have you had a look at pivot charts, They will do exactly what you want.

Each month just refresh the pivot and it's sorted.

 

[SkipVought]

Yes a Pivot table/Pivot Chart would work.

Just give your table a DYNAMIC name range using Insert/Name/Define...

How can I rename a table as it changes size faq68-1331

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]