Cheryl has attended:
Excel Intermediate course
Excel Advanced course
How do i add cells from different workbooks?
How do i add cells from different workbooks?
RE: How do i add cells from different workbooks?
Hi Cheryl
Thank you for your question regarding adding cells from different workbooks.
The syntax of your formula will need to look something like this, with Book 1 and Book 2 being the different workbooks, Sheet 1 and Sheet 2 being the different sheets the data is on within the workbooks and then assuming the data is in cell ref A1
='[Book1]Sheet1'!A1+'[Book2]Sheet2'!$A1
I hope this helps. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post.
Have a great day.
Regards,
Nafeesa
Microsoft Office Specialist Trainer
RE: How do i add cells from different workbooks?
Cheryl,
When working with formulas that link multiple workbooks, I would very strongly recommend that you use named Ranges - which we looked at on the course. That is, the formula to add two values from different workbooks would look like this:
= 'some book 1.xlsx'!Total + 'some book 2.xlsx'!Total
(where Total is a range name in both books), rather than:
= '[some book 1.xlsx!some sheet'!B52 + '[some book 2.xlsx!some sheet'!B52
Not only is the first form more understandable, it is also much more robust in the face of structural changes to the workbooks (e.g. adding or deleting rows). With the cell-address form, formulas only update if both source and destination workbooks are open at the same time, so the liklihood of cross-workbook formulas 'breaking' is quite high.
regards
/Roy
