Graphing Filtered Data +

On Thu, 30 Oct 2003 10:52:42 -0500, sowetoddid =

wrote:

There is a spreadsheet with historical dates in column "A" (ex. 1999,
2000, 2001, etc.). These dates are repeated for about 10 to 15 times
each, depending on the number of data points in that year.

Ex.

1999 $750.00
1999 $1240.00
1999 $1484.00
1999 $345.00
2000 $675.00
2000 $397.00


I am trying to do two things with my data.

1. Graph the data on a line or bar chart (the data is FILTERED).

You should be able to chart it as per normal procedure. One option you =

can check it Tools | Options | Chart tab | Plot only visible cells.

2. Sum the costs for each individual year and then graph the summed
cost in relation to the year. (remember, the data is FILTERED). The
data that is displayed at any given time should be summed and graphed.=


Use the SUBTOTAL function. I think this also answers your followup =

question.

-- =

Dave
dvt at psu dot edu

Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

sowetoddid wrote:
Is there a function or command button that will allow me to transfer the
data on the filtered page (at a given moment in time) to a new sheet??
Then, from that sheet I can run a SUMIF function to get the sum of the
costs based on the respective year.


Does anyone know the visual basic code, or a function to complete
this??

On Thu, 30 Oct 2003 23:26:58 -0500, Jon Peltier =

wrote:

Select a cell in the filtered list, then press Ctrl-* (the asterix in =

the number keypad) to select the contiguous area. If you copy this =

selection and paste it into a new sheet, only the visible rows will be=
=

copied.

But I think it's more useful to follow Dave's suggestion, and make a =

pivot table from the unfiltered list. Work out the filters from the =

pivot fields you choose to show. Then have the pivot table run the su=
ms =

for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

sowetoddid wrote:
Is there a function or command button that will allow me to transfer =
the
data on the filtered page (at a given moment in time) to a new sheet?=
? =

Then, from that sheet I can run a SUMIF function to get the sum of th=
e
costs based on the respective year.

Jon gives me credit for the pivot table idea, but I think it was his =

idea. And a good one at that.

Todd, can you post a sample of your data? I suspect you can get your =

charts by using subtotals and filtering judiciously.

For example, let's say you sort your data by year. Then add subtotals =

(Data | Add subtotals) for each year. Now you filter that data as you d=
id =

before. If you chart the subtotals, only the filtered subtotals will sh=
ow =

up.

-- =

Dave
dvt at psu dot edu

The reasons include the size of the attachment and use of bandwidth, and
the fact that lots of attachments are not benign samples of a
troublesome worksheet. I don't think anyone would gripe, however, if
you included a dozen or two lines pasted as text from your worksheet.
Enough to give us a clue what you're doing.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

sowetoddid wrote:
For whatever reason, I was asked before not to post attachments on this
site. It must bog down the server. I think I will go with the sumif
function though. I think I have way too many columns to be creating a
pivot table.

I will just creat a SUMIF function on another sheet to total each
year's costs. Then from there I will graph based on visible cells.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

hduh
Jon Peltier wrote in message
...
Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

sowetoddid wrote:
Is there a function or command button that will allow me to transfer the
data on the filtered page (at a given moment in time) to a new sheet??
Then, from that sheet I can run a SUMIF function to get the sum of the
costs based on the respective year.


Does anyone know the visual basic code, or a function to complete
this??

I'm sorry, is that "huh" or "duh"?

yulina wrote:
hduh
Jon Peltier wrote in message
...

Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

sowetoddid wrote:

Is there a function or command button that will allow me to transfer the
data on the filtered page (at a given moment in time) to a new sheet??
Then, from that sheet I can run a SUMIF function to get the sum of the
costs based on the respective year.


Does anyone know the visual basic code, or a function to complete
this??