VBA Sum....
 

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\
[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'
c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x
ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra
ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

VBA Sum....
 

Well, You COULD write a formula to do what you suggest. It is possible, but
not recommended..

There may be more details that I'm unaware of, but based on what you said in
the post, I would put ALL the months data in one workbook, on one tab. Add a
column called DateStamp.

It is a VERY good practice to keep the data on a tab all by itself. Very
raw. No formatiing and very accurate.

Use other worksheet tabs to make fancy reports..

From there you can do all kinds of alalytics with built in excel
functionality.

IF sums are positional (ie have to add $a3 on each sheet.) I'd at least
keep all data in one workbook and have a different tab for each day...
Adding those will be easier too (*** there is a LOT of background overhead
with each formula that links to another file. I imagine your workbook is
noticably slow, and will get slower if you add 16 more files it needs to
open) The complexity of the function you suggest is very easy and if
implemented the right way, shouldn't even take a second to recalc
everythign...

HTH..

PS once you get to proper table design in excel, think about moving data
storage to a database..

"John" wrote:

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\
[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'
c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x
ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra
ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

VBA Sum....
 

If you put the sheets all in one workbook (the workbook with the summary
sheet) you could do

=sum(Day1:Day31!A4)

--
Regards,
Tom Ogilvy

"John" wrote in message
...
We currently have a formula that Sums a cell (!$A$3) from 15 different
excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:

=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\

[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'

c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x

ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra

ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs.
0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

VBA Sum....
 

Well I attempted to update one of my formulas, and I ran into the issue of
my Formula is too big for the field....

Ian


"TomHinkle" wrote in message
...
Well, You COULD write a formula to do what you suggest. It is possible,
but
not recommended..

There may be more details that I'm unaware of, but based on what you said
in
the post, I would put ALL the months data in one workbook, on one tab.
Add a
column called DateStamp.

It is a VERY good practice to keep the data on a tab all by itself. Very
raw. No formatiing and very accurate.

Use other worksheet tabs to make fancy reports..

From there you can do all kinds of alalytics with built in excel
functionality.

IF sums are positional (ie have to add $a3 on each sheet.) I'd at least
keep all data in one workbook and have a different tab for each day...
Adding those will be easier too (*** there is a LOT of background overhead
with each formula that links to another file. I imagine your workbook is
noticably slow, and will get slower if you add 16 more files it needs to
open) The complexity of the function you suggest is very easy and if
implemented the right way, shouldn't even take a second to recalc
everythign...

HTH..

PS once you get to proper table design in excel, think about moving data
storage to a database..

"John" wrote:

We currently have a formula that Sums a cell (!$A$3) from 15 different
excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:

=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\

[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'

c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x

ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra

ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic
signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

VBA Sum....
 

Also, I believe the SUM function only accepts 30 arguments.

"John" wrote:

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\
[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'
c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x
ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra
ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

VBA Sum....
 

formulas are restricted to 1024 characters, measured when the formula is in
R1C1 format.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Well I attempted to update one of my formulas, and I ran into the issue of
my Formula is too big for the field....

Ian


"TomHinkle" wrote in message
...
Well, You COULD write a formula to do what you suggest. It is possible,
but
not recommended..

There may be more details that I'm unaware of, but based on what you
said
in
the post, I would put ALL the months data in one workbook, on one tab.
Add a
column called DateStamp.

It is a VERY good practice to keep the data on a tab all by itself.
Very
raw. No formatiing and very accurate.

Use other worksheet tabs to make fancy reports..

From there you can do all kinds of alalytics with built in excel
functionality.

IF sums are positional (ie have to add $a3 on each sheet.) I'd at least
keep all data in one workbook and have a different tab for each day...
Adding those will be easier too (*** there is a LOT of background
overhead
with each formula that links to another file. I imagine your workbook
is
noticably slow, and will get slower if you add 16 more files it needs to
open) The complexity of the function you suggest is very easy and if
implemented the right way, shouldn't even take a second to recalc
everythign...

HTH..

PS once you get to proper table design in excel, think about moving data
storage to a database..

"John" wrote:

We currently have a formula that Sums a cell (!$A$3) from 15 different
excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:


=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\


[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'


c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x


ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra


ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic
signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian