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
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
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1