LOOKUP Problem

[grey]

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!

 

[Guest]

Add a new column to the sheet and put the week number in it. Instead of
saving the data to 52 different files, append each week's data to the end of
a master file. At the end of the year, or at any time during the year,
create a pivot table from the master file's data, with the week numbers
across the top

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]

Add a new column to the sheet and put the week number in it. Instead
of
saving the data to 52 different files, append each week's data to the
end of
a master file. At the end of the year, or at any time during the
year,
create a pivot table from the master file's data, with the week
numbers
across the top

Unfortunately, due to the way the current system works and the other
things that the weekly files are used for (they contain far more data
than my example) there is no way that the process can function without
having 52 seperate weekly files

 

[Guest]

Well then, you have a choice to make.

One: leave all your data in 52 workbooks and do nohting else. Spend LOTS &
LOTS of time and effort to try to pull it all together without dropping any
names. Spend LOTS & LOTS of time and effort trying to validate you've done
it correctly. Trying to link to 52 as-yet-nonexistent files seems like the
HARDEST way to get where you need to be.

Two: do something with the data to make it simple to summarize.

My suggestion was only one of probably half a dozen ways to simplify it.
Open your mind up to other ways of doing things.

 

[grey]

OK, well here's my problem in simplifying the data..

- Each file currently contains information about each person (I realis
that this should really be stored speperately from each week's data, bu
that doesnt really impact this problem)

- In each file, there are 3 tabs of information (they have to be kep
seperate for process to run)

- For each week, there is a number for each day, plus the three total
that it leads to..

eg
Mon--Tues--Weds--Thurs--Fri--Sat--Sun--TOTAL1--TOTAL2--TOTAL3

In order to put this all into one file, I would have to have the nam
(and the details about that person) followed by 365 days, and 3 total
(x 52, as there are 3 totals for each week)

- The list contains hundreds of names, and can change on a dail
basis...for the purposes of inputting the numbers each day, inputtin
the numbers into the full list of people would be very difficult t
manage. In addition to this, the data is very much managed on a weekl
basis.

Obviously this makse the management of the data quite difficult
particular considering that the peopel using it have very limite
computer experience and ahve to fit in with another manual system


I think I've just pretty much answered my own question in that i
cannot be done, lo