+ Reply to Thread
Results 1 to 2 of 2

Data from other workbooks

  1. #1
    Registered User
    Join Date
    07-25-2007
    Posts
    41

    Data from other workbooks

    Hello,

    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

    Dan

  2. #2
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    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
    =[Book2]Sheet1!$A$1
    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
    Mark.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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