consolidating workbooks

[Guest]

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???

 

[dominicb]

Good afternoon Chris Confused

You could use an array formula.

Highlight the range on your master sheet where you expect the values o
the formuale to go. Press "=" and then highlight your range on you
first source sheet (which must be the same size), press "+" an
highlight the range on your second source sheet, and so on. When you
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 d
what you want and it's quicker than doing it cell by cell.

HTH

Dominic

 

[Guest]

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