I have a file containing a list of names, and the quantities associated
with that person for that particular week.

e.g.
Week1.xls |
[##NAME##][##QUANTITY A##][##QUANTITY B##][#TOTAL#]
[--Alan--][------5-------][------2-------][---7---]
[-Claire-][------1-------][------0-------][---1---]
[-Daniel-][------3-------][------5-------][---8---]
[-Edward-][------0-------][------0-------][---0---]
[--Jane--][------0-------][------1-------][---1---]

At the end of each week, the file is copied, and the quantites are
removed, ready for the details to be added the next week
(called week 2.xls). At the end of the year we are then left with 52
"week #.xls" files.

At any time during the week, new people can join and old people people
may leave (and will be added/removed accordingly)

For this reason, it is impossible to create all of the files for the
year in advance, as the list of names needs to be
copied each week from the previous week.

My goal is to create a summary file. At the end of the year, this file
will contain all of the names from the year
(even those who have left) and bring together the totals from each
week.
e.g.

_________
Week1.xls | |
[##NAME##][##QUANTITY A##][##QUANTITY B##][#TOTAL#] |
[--Alan--][------5-------][------2-------][---7---] |
[-Claire-][------1-------][------0-------][---1---] |
[-Daniel-][------3-------][------5-------][---8---] | Note: During
week 2
[-Edward-][------0-------][------0-------][---0---] | Daniel and
Jane left,
[--Jane--][------0-------][------1-------][---1---] | and Brian and
Edmund
__________________________________________________ ______________|
joined.
|
| ________
| Week2.xls | |
| [##NAME##][##QUANTITY A##][##QUANTITY B##][#TOTAL#] |


| [--Alan--][------2-------][------2-------][---4---] |
| [--Brian-][------1-------][------1-------][---2---] |
| [-Claire-][------0-------][------0-------][---0---] |
| [-Edmund-][------1-------][------1-------][---2---] |
| [-Edward-][------2-------][------4-------][---6---] |
| |
| ________________________________|
|_______________________ |
| |
| |
Summary.xls v v
[##NAME##][##WEEK 1##][##WEEK 2##][#TOTAL#]
[--Alan--][----7-----][----4-----][---11--]
[--Brian-][----NA----][----2-----][---2---]
[-Claire-][----1-----][----0-----][---1---]
[-Daniel-][----8-----][----NA----][---8---]
[-Edmund-][----NA----][----2-----][---2---]
[-Edward-][----0-----][----6-----][---6---]
[--Jane--][----1-----][----NA----][---1---]

The real life problem means that it is necessary to have a seperate
file for each week.

My initial thought was to fill the cells in the summary file with
LOOKUP functions, which would look up the name
from the first column in each week's file and return the toatl for that
person for that week. However, there are
2 problems with this approach:

- I need to create the summary file in advance, as the person using
it does not have experience
with these sorts of functions. I cannot do this and have each cell
link to a file which has not
yet been created (as previously stated, the weekly files must be
created on a weekly basis)

- Due to the fact that the list of names is constantly changing, not
all of the names in the summary
file are in every weekly file. The result of this is that the wrong
values are returned for these
names (rather than returning NA or FALSE)
e.g. Jane in week 2 returns 6 as the total

Is it possible to solve this problem, or is it likely to end up being
more work than just adding things up manually?

Thanks!


--
grey
------------------------------------------------------------------------
grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
View this thread: http://www.excelforum.com/showthread...hreadid=496388