LinkBack URL
About LinkBacksWell, you could use a Data Table (Data>Table...). Your Sum(B:B) formula
would be at the top of the table and the dates would go down the left column.
It'll take a long time to calculate, but should do the trick. I'd try it
with a samll sample of dates first - maybe 3 to 5 dates - and then expand it
to the full range of dates if it gives you what you want.
Personally, I'd see about the possibility of putting this into a database -
Access, MSDE, or SQL Server - and running queries or reports against the
database. That's one area where databases have an enormous advantage over
Excel.
"Nimit Mehta" wrote:
> Hello,
> A folder contains about 1200 workbooks, named based on client's account
> numbers who make international calls.
>
> A workbook named 31648.xls looks like this.
>
> Mark Twain
> 31648
> DATE TIME COUNTRY RATE MINUTES AMOUNT
>
> 1-Jan-05 12:34 PM USA 1.95 6 11.70
> 1-Jan-05 11:20 PM USA 1.95 10 19.50
> 2-Jan-05 10:12 AM USA 1.95 4 7.80
> 3-Jan-05 11:03 AM USA 1.95 2 3.90
> 3-Jan-05 8:25 PM Canada 3.00 1 1.95
> 4-Jan-05 7:53 PM USA 1.95 10 19.50
> 4-Jan-05 10:04 PM UK 2.00 7 13.65
> 4-Jan-05 10:11 PM USA 1.95 13 25.35
> 5-Jan-05 10:17 PM USA 1.95 13 25.35
> 5-Jan-05 10:36 PM USA 1.95 23 44.85
> 6-Jan-05 7:58 AM USA 1.95 2 3.90
> 6-Jan-05 7:44 PM USA 1.95 6 11.70
> 6-Jan-05 8:50 PM USA 1.95 22 42.90
> 7-Jan-05 11:09 PM USA 1.95 7 13.65
> 9-Jan-05 10:51 PM AUS 3.00 3 5.85
>
> I have made another workbook to get details of minutes consumed on a
> perticular date from all 1200 workbooks using sumproduct funtion. This sheet
> looks like this.
>
>
> A B
> F G
> NAME MINUTES 9-Jan-05 1917.00
> 31648 3.00
> 31734 0.00
> 31777 0.00
> 31820 0.00
> 31863 0.00
> 31906 13.00
> 31949 0.00
> 31992 0.00
> 32078 0.00
> 32121 0.00
> 32207 0.00
> 32293 10.00
> to 1200..
>
> "Minutes" coloumn above contains this function for 31906.xls. (Account
> number changes from cell to cell.)
> =SUMPRODUCT(('F:\CONFERENCE\[31906.xls]Sheet1'!$A$5:$A$500=$F$1)*('F:\CONFERENCE\[31906.xls]Sheet1'!$E$5:$E$500))
>
> G1 contains =SUM(B:B)
>
> Right now, when i type a date in F1, sumproduct gets total minutes consumed
> by all 1200 clients on that perticular date. What i want is a coloumn "DATE"
> and coloumn next to it should display the minutes consumed on that perticular
> date.
>
> Like this :
>
> I enter date in coloumn A and get minutes automatically in coloumn B.
>
> Tuesday, May 24, 2005 1559
> Wednesday, May 25, 2005 1721
> Thursday, May 26, 2005 1789
> Friday, May 27, 2005 1699
> Saturday, May 28, 2005 2648
> Sunday, May 29, 2005 2460
> Monday, May 30, 2005 1940
> Tuesday, May 31, 2005 1719
> Wednesday, June 01, 2005 1793
> Thursday, June 02, 2005 1396
> Friday, June 03, 2005 1794
> Saturday, June 04, 2005 1970
> Sunday, June 05, 2005 2745
> Monday, June 06, 2005 1207
> Tuesday, June 07, 2005 1917
>
> Right now i am manually inserting dates in coloumn B, after getting the
> minutes for individual dates using the above sheet. This is a very slow
> process, because once i enter a date in F1, sheet takes about 2 minutes to
> calculate minutes on that perticular Date.
>
> Suggestions?
> Thanks.
Register To Reply
Bookmarks