Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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??? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate different sheets to different workbooks | Excel Worksheet Functions | |||
Consolidating multiple workbooks | Excel Worksheet Functions | |||
Consolidating data from different workbooks | Excel Worksheet Functions | |||
Linking Workbooks | Excel Worksheet Functions | |||
Workbooks...I'll try this again... | Excel Discussion (Misc queries) |