Chart Account# and $ value from MANY into Line Chart
 

Hello,

I have 365 excel files with the first page containing 2 columns. An account
number and the cash in the account.
Everyday a new account could be added or removed from the sheet.

What is the best way to pull all these files into one graph?

How could I further filter the accounts by saying only account Numbers
ending in a B or an A.

How can I show on the line chart when an account drops off the chart.

I'd suggest a routine that pulled all the data into a single sheet, inserted
an extra column and used the filename as an identifier so that you ended up
with 3 columns of data. Then dump the lot in a Pivot table and let it do
all the hard work.

Are all the files in a single directory, or in a folder structure of months
perhaps. What kind of numbers are we talking wrt how many rows per sheet -
just on average.

Any reason for them all being in separate files - why not single sheets in
one file? - why not all on one sheet? - I'm assuming the 365 corresponds to
days of the year, so historical stuff won't change will it?

Examples of the filenames please, and are there any other Excel files in the
folder structure besides these 365 files?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"P. G." <P. wrote in message
...
Hello,

I have 365 excel files with the first page containing 2 columns. An
account
number and the cash in the account.
Everyday a new account could be added or removed from the sheet.

What is the best way to pull all these files into one graph?

How could I further filter the accounts by saying only account Numbers
ending in a B or an A.

How can I show on the line chart when an account drops off the chart.

Hello,

The files are thrown into a year directory.

MMDDYY

Can I do what you suggst and throw it into a text file and then have the
report read that file. Excel file is limitted in how may records you can have
(isnt it?)

There are currently 1200 records for todays file 500 records in the first of
the years.

So I guess i need to know how to group the files into one file and then how
to read that into a chart.



"Ken Wright" wrote:

I'd suggest a routine that pulled all the data into a single sheet, inserted
an extra column and used the filename as an identifier so that you ended up
with 3 columns of data. Then dump the lot in a Pivot table and let it do
all the hard work.

Are all the files in a single directory, or in a folder structure of months
perhaps. What kind of numbers are we talking wrt how many rows per sheet -
just on average.

Any reason for them all being in separate files - why not single sheets in
one file? - why not all on one sheet? - I'm assuming the 365 corresponds to
days of the year, so historical stuff won't change will it?

Examples of the filenames please, and are there any other Excel files in the
folder structure besides these 365 files?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"P. G." <P. wrote in message
...
Hello,

I have 365 excel files with the first page containing 2 columns. An
account
number and the cash in the account.
Everyday a new account could be added or removed from the sheet.

What is the best way to pull all these files into one graph?

How could I further filter the accounts by saying only account Numbers
ending in a B or an A.

How can I show on the line chart when an account drops off the chart.

Sorry but you have me slightly confused as to numbers.

Yes there are only 65Kish rows in a worksheet

When you say 1200 records for todays file and 500 for first years I'm not
quite sure what you mean. Is that 1200 purely for today, and then possibly
1200 for tomorrow and so on, so potentially 1200*365 records? What is the
500?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Hi,

Everyday we are openning new accounts.
Jan 1st 2004 we had 500, today we have 1200, tomorrow maybe 1207 etc...
Some people close there accounts too.

Regards.

"Ken Wright" wrote:

Sorry but you have me slightly confused as to numbers.

Yes there are only 65Kish rows in a worksheet

When you say 1200 records for todays file and 500 for first years I'm not
quite sure what you mean. Is that 1200 purely for today, and then possibly
1200 for tomorrow and so on, so potentially 1200*365 records? What is the
500?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip