Hi!
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!!! I switched off the automatic update and the source files are
in another folder (so Excel can't find them).
Example: I open the new consolidated file year1 and the values are all okay,
i.e. the sums of source1 one and source2. But as soon as i open the workbook
year2 the values in year1 change and the values for year2 seem to be random,
too.

Anyone had this problem before? How to solve it (without changing all cells
to value only manually)?

Hi Chris

If you do not want historic data to change, you should Copy and Paste
Special as values. What happens here, is that Excel is doing what it is
designed to do. You still have formulae in your history sheets, and these
now updatewith the open file data.

--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

Hi!
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!!! I switched off the automatic update and the source files are
in another folder (so Excel can't find them).
Example: I open the new consolidated file year1 and the values are all okay,
i.e. the sums of source1 one and source2. But as soon as i open the workbook
year2 the values in year1 change and the values for year2 seem to be random,
too.

Anyone had this problem before? How to solve it (without changing all cells
to value only manually)?

thanks for your reply Kassie.
And as long as only one consolidated workbook (eg year1) is open - no
problem!
But as soon as I open another consolidted one (filename not even similar to
the first one or even the source-files which are closed and on another
drive!!) EVERYTHING gets mixed up.... So Excel updates randomly???

How can I stop Excel from updating? Apart from copy and paste special for
every cell?
Each workbook contains about 15 sheets and on each sheet there are about 400
cells to be changed (=copy and paste special). The problem is that i can only
change a max. of 4 cells a time because there are lots of blocked cells in
between which I can not and do not want to change because they contain the
essential formulas (which are not linked to other sheets...)
That is why i created one workbook ("master workbook" used for the
consolidation and then i save the file under another name to use that
"masterworkbook" for the next consolidation) which I am using for summing up
values from the workbooks "Source1" and "Source2". These sources change their
values whenever i make the consolidation for another year and that is why I
don't want Excel to update.


"Kassie" wrote:

Hi Chris

If you do not want historic data to change, you should Copy and Paste
Special as values. What happens here, is that Excel is doing what it is
designed to do. You still have formulae in your history sheets, and these
now updatewith the open file data.

--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

Hi!
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!!! I switched off the automatic update and the source files are
in another folder (so Excel can't find them).
Example: I open the new consolidated file year1 and the values are all okay,
i.e. the sums of source1 one and source2. But as soon as i open the workbook
year2 the values in year1 change and the values for year2 seem to be random,
too.

Anyone had this problem before? How to solve it (without changing all cells
to value only manually)?

Hi

Without having insight into the formulae you use, I would not be able to
correctly pinppoint the source of your problem. The best practice remains to
copy/paste special as values.
--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

thanks for your reply Kassie.
And as long as only one consolidated workbook (eg year1) is open - no
problem!
But as soon as I open another consolidted one (filename not even similar to
the first one or even the source-files which are closed and on another
drive!!) EVERYTHING gets mixed up.... So Excel updates randomly???

How can I stop Excel from updating? Apart from copy and paste special for
every cell?
Each workbook contains about 15 sheets and on each sheet there are about 400
cells to be changed (=copy and paste special). The problem is that i can only
change a max. of 4 cells a time because there are lots of blocked cells in
between which I can not and do not want to change because they contain the
essential formulas (which are not linked to other sheets...)
That is why i created one workbook ("master workbook" used for the
consolidation and then i save the file under another name to use that
"masterworkbook" for the next consolidation) which I am using for summing up
values from the workbooks "Source1" and "Source2". These sources change their
values whenever i make the consolidation for another year and that is why I
don't want Excel to update.


"Kassie" wrote:

Hi Chris

If you do not want historic data to change, you should Copy and Paste
Special as values. What happens here, is that Excel is doing what it is
designed to do. You still have formulae in your history sheets, and these
now updatewith the open file data.

--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

Hi!
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!!! I switched off the automatic update and the source files are
in another folder (so Excel can't find them).
Example: I open the new consolidated file year1 and the values are all okay,
i.e. the sums of source1 one and source2. But as soon as i open the workbook
year2 the values in year1 change and the values for year2 seem to be random,
too.

Anyone had this problem before? How to solve it (without changing all cells
to value only manually)?

the formula is like the following:
[CONSOLIDATEDworkbook.xls]sheet1.A1=[Source1.xls]sheet1'!A1+[Source2.xls]sheet1'!A1
[CONSOLIDATEDworkbook.xls]sheet1.A2=[Source1.xls]sheet1'!A2+[Source2.xls]sheet1'!A2

I also think copy and paste special/values is the best way, I was just
wondering if there was something like a function doing that automatically and
saving me a HUGE amount of time...


"Kassie" wrote:

Hi

Without having insight into the formulae you use, I would not be able to
correctly pinppoint the source of your problem. The best practice remains to
copy/paste special as values.
--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

thanks for your reply Kassie.
And as long as only one consolidated workbook (eg year1) is open - no
problem!
But as soon as I open another consolidted one (filename not even similar to
the first one or even the source-files which are closed and on another
drive!!) EVERYTHING gets mixed up.... So Excel updates randomly???

How can I stop Excel from updating? Apart from copy and paste special for
every cell?
Each workbook contains about 15 sheets and on each sheet there are about 400
cells to be changed (=copy and paste special). The problem is that i can only
change a max. of 4 cells a time because there are lots of blocked cells in
between which I can not and do not want to change because they contain the
essential formulas (which are not linked to other sheets...)
That is why i created one workbook ("master workbook" used for the
consolidation and then i save the file under another name to use that
"masterworkbook" for the next consolidation) which I am using for summing up
values from the workbooks "Source1" and "Source2". These sources change their
values whenever i make the consolidation for another year and that is why I
don't want Excel to update.


"Kassie" wrote:

Hi Chris

If you do not want historic data to change, you should Copy and Paste
Special as values. What happens here, is that Excel is doing what it is
designed to do. You still have formulae in your history sheets, and these
now updatewith the open file data.

--
ve_2nd_at. Randburg, Gauteng, South Africa


"chris confused" wrote:

Hi!
I created a worksheet which is linked to two different "source" workbooks to
consolidate data. I am using this workbook for several consolidations (same
format and i just save the changed data under a file "source 1" and "source
2" for then automatically obtaining the sums in my CONSOLIDATION workbook)
and that works fine.
But after consolidating different workbooks and obtaining 3 different
CONSOLIDATED files (which i save under another name after consolidation) for
year1, year2 and year3, the values in those files change as soon as i open
another one!!! I switched off the automatic update and the source files are
in another folder (so Excel can't find them).
Example: I open the new consolidated file year1 and the values are all okay,
i.e. the sums of source1 one and source2. But as soon as i open the workbook
year2 the values in year1 change and the values for year2 seem to be random,
too.

Anyone had this problem before? How to solve it (without changing all cells
to value only manually)?