Charting selective data

[Jael]

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

 

[B. R.Ramachandran]

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

 

[Jon Peltier]

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

 

[Jael]

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

 

[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

 

[Jon Peltier]

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

 

[Jael]

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

 

[Jon Peltier]

Sure. Turn on the macro recorder then play with the autofilter to get some
code to play with.

- Jon

 

[Jael]

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

Jeff