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

[bj]

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

Are all of the worksheets in the same workbook?

"Stephen McArthu" wrote:

>
> 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
>
>
> --
> Stephen McArthu
> ------------------------------------------------------------------------
> Stephen McArthu's Profile: http://www.excelforum.com/member.php...o&userid=24054
> View this thread: http://www.excelforum.com/showthread...hreadid=376688
>
>

[Stephen McArthu]

Thanks for replying,

There are 12 folder, one for each month, with a subfolder for the month 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 whole lot when transferred between computers.

I hope you can help....Thanks again

Stephen McArthur

[bj]

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+ ...


"Stephen McArthu" wrote:

>
> Thanks for replying,
>
> There are 12 folder, one for each month, with a subfolder for the month
> 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 whole
> lot when transferred between computers.
>
> I hope you can help....Thanks again
>
> Stephen McArthur
>
>
> --
> Stephen McArthu
> ------------------------------------------------------------------------
> Stephen McArthu's Profile: http://www.excelforum.com/member.php...o&userid=24054
> View this thread: http://www.excelforum.com/showthread...hreadid=376688
>
>

[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