• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summarising Data from Multiple Workbooks - Excel 2013

A

AlanFisher

New Member
Hi. I have six workbooks, one for each of the last six years - 2010 to 2015. Each workbook has twelve identical tabs, one for each month. I want to create a summary workbook that takes the data from cell A6 (for example), from the January sheets in each year and places the summarised total in a new workbook. Then the same for February, and so on.

I know how to manually link these workbooks together but I'm wondering if there's a quicker method - but without using VBA.

Your help would be much appreciated. Alan.
 
Absolutely !! =[PFE_Depot_2016.xlsx]Sites!$A$6
Write your formulas as normal, but instead of selecting another worksheet, you alt-tab the other work book... When you open your finished product, Excel will ask you if you want to update your data, if you say yes it will gather the latest data from those workbooks... You can say no if there has been no updates and the last know data will be present...
 
PaulF said:
Absolutely !! =[PFE_Depot_2016.xlsx]Sites!$A$6
Write your formulas as normal, but instead of selecting another worksheet, you alt-tab the other work book... When you open your finished product, Excel will ask you if you want to update your data, if you say yes it will gather the latest data from those workbooks... You can say no if there has been no updates and the last know data will be present...
Click to expand...

Hi. Thanks for replying so quickly. My apologies if I haven't understood your solution correctly but my original problem is that I have to link cells from 72 different worksheets (6 books, 12 sheets per book) onto a summary worksheet. The only method I know is to link them all manually but does your suggestion not require the same amount of manual linking to create the summary worksheet?

Again - sorry if I'm being a bit dim here but I was hoping there was a way I could do this without all the manual linking. Alan
 
Try using Indirect Function. See the Attachment.

PS: Initially, it may show #Ref. You need to replace the Workbook names and Sheet names with actual. Then it works perfectly.
 

Attachments

You must log in or register to reply here.
Back
Top