Charting selective data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database in an excel worksheet that I download from an internet
source.
It's 25 columns by 100 to 150 rows.

I want to plot the value in the "Value" column versus the date in the "Date"
column for each row where the "Status" Column equals "A" in a chart on that
worksheet.

"Value" ranges form 100 to 300, "Date" covers a 2 year window and there are
about 30 (+/-) occurrences of "Status" = "A"

I use the data base functions to find min, max, average but would like some
way to apply the criteria to my graph.

I'm familiar with MS Access & VBA in MS Access and could generate a query
but I want to do this in an Excel application.

Any suggestions would be great!

Thanks,

Jael
 
Hi,

Select entire page and sort the data by the "Status" column. All rows with
Status = "A" will bunch up into a block. Now you can selectively plot
"Value" vs "Date" for that block.

Before doing the above, you could copy the entire data to another sheet, so
that the original data structure is in tact.

Regards,
B. R. Ramachandran
 
Apply an autofilter to the data range, or convert it to a list (Excel 2003:
Data menu) or a table (Excel 2007), then select the status you want to show
in the dropdown. By default, Excel charts do not plot hidden rows and
columns.

- Jon
 
Thanks for the reply - That's what I currently do, but I have to re-define
the graph's range each time I download (two to three times a week) as they
never have the same number of rows.

jael
 
Thanks - not familiar with autofilter but search it out and give it a try -
I'm using Excel 2000 (yeah I know - it's old, but the company is too cheap to
upgrade) will that make a difference?

jael
 
No problem. AutoFilter works the same way in Excel from version 97 (and
probably earlier, I just don't remember) to 2003. It also works in 2007, but
I'm not sure if the functionality has been adjusted much.

- Jon
 
Jon:
Thanks - works well for what I need at this time. Can Autofilter be applied
programatically? I'm familian with VBA in MS Access but not in Excel and
this might be a good place to start "experimenting"

Apreciate your responses
Jael
 
Sure. Turn on the macro recorder then play with the autofilter to get some
code to play with.

- Jon
 
Thanks again - that's how I got started with VBA in Access - must be addled
in my old age.

Jeff
 
Back
Top