+ Reply to Thread
Results 1 to 2 of 2

Data from other workbooks

  1. #1
    Registered User
    Join Date

    Data from other workbooks


    I am quite a newbie and have looked through many previous posts and not quite found what i am looking for, so here goes:

    I have 6 sales people who all fill in a spreadsheet once a week. each workbook is called SAT then their name eg SAT Dan, SAT George etc. once they have filled this in they email it to me. what i want to do is be able to take all the data from one worksheet of each of their workbooks and have a sum total of their achievements. how do i do this? i know it will be quite simple to most but i have tried the excel help and got more confused than when i forst started!!

    thanks in avdvance


  2. #2
    Forum Contributor
    Join Date
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Lets take this in stages, open up 2 blank workbooks, (Book1 and Book2)
    in cell A1 of Book1 type = then click on the cell A1 in Book2.
    You should get
    You need to reproduce this style of address to access the work books of your sales team.
    ok now type SAT Dan.xls into cell A2 (of book1)
    type Sheet1 into cell A3
    type ="["&A2&"]"&A3 into cell A4
    you should get [SAT Dan.xls]Sheet1
    type =ADDRESS(1,1,,,A4) into cell E4
    you should get '[SAT Dan.xls]Sheet1'!$A$1
    Note the 2 ' these are required because there is a space in the name.
    type ="'"&A4&"'!A1:A10" into cell A5
    you should get '[SAT Dan.xls]Sheet1'!A1:A10
    Finally type: =SUM(INDIRECT(A5)) into cell A6
    this will give you the sum of the cells A1 to A10 from sheet1 of 'SAT Dan.xls'
    Hope this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1