consolidating workbooks
 

Hi!

I have two xls files for the reporting, both identical apart from the
numbers in the various cells. Each file has 10 different workbooks containing
text, number and formula cells.
I have to merge/consolidate these files to get the sum of all data. I
already tired the "consolidate" function in the Data menu but that doesn'
work because i loose the text and the formatting. I would also like to keep
the formulas in the new file/workbook but instead i only got numbers using
"consolidate".

Is there a way I can easily copy and consolidate everything or do I have to
copy formula and text cells and then consolidate or sum up the other cells
manually???

Good afternoon Chris Confused

You could use an array formula.

Highlight the range on your master sheet where you expect the values of
the formuale to go. Press "=" and then highlight your range on your
first source sheet (which must be the same size), press "+" and
highlight the range on your second source sheet, and so on. When your
formula is complete press ctrl + shift + enter to commit it.

It's a bit tricky to describe well, but try it - I think it will do
what you want and it's quicker than doing it cell by cell.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=386746

thanks for the help dominicb.
i tried this with several files and I got sums of the fields. But
unfortunately I lost all formulas. Actually not a big problem coz I can
update it. The problem I have is that your method only works if all fields
are unlocked or all fields are locked. The other way to do it (Data --
Consolidate) works better for me at the moment, and i find it easier to
handle (just a couple of clicks) but in the end it's the same function I
guess. With both ways, I'm loosing all formulas and the text describing the
content of the cells...

"dominicb" wrote:


Good afternoon Chris Confused

You could use an array formula.

Highlight the range on your master sheet where you expect the values of
the formuale to go. Press "=" and then highlight your range on your
first source sheet (which must be the same size), press "+" and
highlight the range on your second source sheet, and so on. When your
formula is complete press ctrl + shift + enter to commit it.

It's a bit tricky to describe well, but try it - I think it will do
what you want and it's quicker than doing it cell by cell.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=386746