Calculations crossing multiple sheets

[Stephen McArthu]

Hello,
I have made a spreadsheet which takes calculations from many
other spreadsheets in the same folder. I have positioned the folder on
my hard drive so that I can copy it onto multiple computers (I.E. it is
placed on the "C" drive, with no PC specific routeing). The problem I
have is that when I copy the file from one PC to another PC, the
calculations change..the specific calculations (=sum(..)) are changed.
Is there any way I can stop this, forcing the calculations to remain
exactly as I have inputted them.

Any help would be very much appreciated as I have spent a lot of time
doing this.

There is 31 Data input sheets with Daily Totals(Days)
There is 12 Data collection sheets (Months)
There is 1 master reference sheet (Year)

Many Thanks...

Stephen McArthur

 

[Guest]

Would you give an example of an equation before and after the change?

Are all of the worksheets in the same workbook?

 

[Stephen McArthu]

Thanks for replying,

There are 12 folder, one for each month, with a subfolder for the mont
end totals.
A thirteenth folder for the year.

A typical example of the change would be:

=SUM('[01.xls]MOVE & HANDLE'!$D$3,'[02.xls]MOVE
HANDLE'!$D$3,'[03.xls]MOVE & HANDLE'!$D$3,'[04.xls]MOVE
HANDLE'!$D$3,'[05.xls]MOVE & HANDLE'!$D$3,'[06.xls]MOVE
HANDLE'!$D$3,'[07.xls]MOVE & HANDLE'!$D$3,'[08.xls]MOVE
HANDLE'!$D$3,'[09.xls]MOVE & HANDLE'!$D$3,'[10.xls]MOVE
HANDLE'!$D$3,'[11.xls]MOVE & HANDLE'!$D$3,'[12.xls]MOVE
HANDLE'!$D$3,'[13.xls]MOVE & HANDLE'!$D$3,'[14.xls]MOVE
HANDLE'!$D$3,'[15.xls]MOVE & HANDLE'!$D$3,'[16.xls]MOVE
HANDLE'!$D$3)+SUM('[17.xls]MOVE & HANDLE'!$D$3,'[18.xls]MOVE
HANDLE'!$D$3,'[19.xls]MOVE & HANDLE'!$D$3,'[20.xls]MOVE
HANDLE'!$D$3,'[21.xls]MOVE & HANDLE'!$D$3,'[22.xls]MOVE
HANDLE'!$D$3,'[23.xls]MOVE & HANDLE'!$D$3,'[24.xls]MOVE
HANDLE'!$D$3,'[25.xls]MOVE & HANDLE'!$D$3,'[26.xls]MOVE
HANDLE'!$D$3,'[27.xls]MOVE & HANDLE'!$D$3,'[28.xls]MOVE
HANDLE'!$D$3,'[29.xls]MOVE & HANDLE'!$D$3,'[30.xls]MOVE
HANDLE'!$D$3,'[31.xls]MOVE & HANDLE'!$D$3)


The change would be the final cell..i.e. $D$3 would become $E$3,

And as ther is multiple calculations per sheet, this ruins the whol
lot when transferred between computers.

I hope you can help....Thanks again

Stephen McArthu

 

[Guest]

I think the problem is that he sum function only allows 30 arguements in the
parenthsis and you have 31.
I do not think you need the sum function.
Just use
='[01.xls]MOVE & HANDLE'!$D$3+'[02.xls]MOVE & HANDLE'!$D$3+'[03.xls]MOVE &
HANDLE'!$D$3+'[04.xls]MOVE & HANDLE'!$D$3+'[05.xls]MOVE & HANDLE'!$D$3+ ...

 

[PokerZan]

Please forgive me if this is a bit basic, but wouldn't it be easier to
just keep one master file and do pivot tables to calculate daily and
weekly totals quickly when you need reports?

If you have then a standard format that you use for reporting you could
dump the data files into a template or macro to format the reports the
way you want.

Just a question/suggestion if you haven't thought of it.

PZan