Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Problem
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Problem
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 "grey" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Problem
Duke Carey Wrote: 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 -- grey ------------------------------------------------------------------------ grey's Profile: http://www.excelforum.com/member.php...o&userid=17951 View this thread: http://www.excelforum.com/showthread...hreadid=496388 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Problem
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" wrote: Duke Carey Wrote: 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 -- grey ------------------------------------------------------------------------ grey's Profile: http://www.excelforum.com/member.php...o&userid=17951 View this thread: http://www.excelforum.com/showthread...hreadid=496388 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Problem
OK, well here's my problem in simplifying the data.. - Each file currently contains information about each person (I realise that this should really be stored speperately from each week's data, but that doesnt really impact this problem) - In each file, there are 3 tabs of information (they have to be kept seperate for process to run) - For each week, there is a number for each day, plus the three totals 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 name (and the details about that person) followed by 365 days, and 3 totals (x 52, as there are 3 totals for each week) - The list contains hundreds of names, and can change on a daily basis...for the purposes of inputting the numbers each day, inputting the numbers into the full list of people would be very difficult to manage. In addition to this, the data is very much managed on a weekly basis. Obviously this makse the management of the data quite difficult, particular considering that the peopel using it have very limited computer experience and ahve to fit in with another manual system I think I've just pretty much answered my own question in that it cannot be done, lol -- grey ------------------------------------------------------------------------ grey's Profile: http://www.excelforum.com/member.php...o&userid=17951 View this thread: http://www.excelforum.com/showthread...hreadid=496388 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions |