consolidating workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

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???
 
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
 
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...
 
Back
Top