| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, sumproduct, help by date
Hello,
This first chart is where I am entering names by day (sheet one). Please notice that not all fields are filled in (this will vary by day). Name 1 Name 2 Name 3 Name 4 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006 Smith Jones Cooper 1/6/2006 Smith Jones 1/7/2006 Jones Cooper 1/8/2006 1/9/2006 Jones Cooper 1/10/2006 1/11/2006 Jones 1/12/2006 Smith Jones Cooper Smith 1/13/2006 Jones 1/14/2006 Cooper Smith 1/15/2006 Smith Jones Cooper Smith 1/16/2006 What I need help with is writing a formula that will bring the names from sheet 1 into a table I will keep on sheet 2. The names need to come over by weekending: for example in the chart below I need to have the names listed on 1/15 first, then the 14th, 13th and so on all the way to the 9th. There could be as many as 20 names per day (line), and I also need help getting past hte null cells. Sheet two will have a start and end date listed on the page. Week ending: January 15, 2006 Smith Jones Cooper Smith Cooper Smith Jones Smith Jones Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if, sumproduct, help by date
Hi!
Your bottom "chart" doesn't seem to jive with your explanation and the data posted in the top "chart". Based on my understanding, wouldn't it be: Week ending: January 15, 2006 15 = Smith Jones Cooper Smith 14 = Cooper Smith 13 = Jones 12 = Smith Jones Cooper Smith 11 = Jones 9 = Jones Cooper This is fairly easy to do but requires "complicated formulas" that may effect the overall performance of your file. How big of a dataset has to be searched on Sheet1 in order to extract the required data? If there may be up to 20 names per day, then you'll need 20*7 formulas. That's not a lot if this is all your file is doing. Biff "Jim" wrote in message ... Hello, This first chart is where I am entering names by day (sheet one). Please notice that not all fields are filled in (this will vary by day). Name 1 Name 2 Name 3 Name 4 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006 Smith Jones Cooper 1/6/2006 Smith Jones 1/7/2006 Jones Cooper 1/8/2006 1/9/2006 Jones Cooper 1/10/2006 1/11/2006 Jones 1/12/2006 Smith Jones Cooper Smith 1/13/2006 Jones 1/14/2006 Cooper Smith 1/15/2006 Smith Jones Cooper Smith 1/16/2006 What I need help with is writing a formula that will bring the names from sheet 1 into a table I will keep on sheet 2. The names need to come over by weekending: for example in the chart below I need to have the names listed on 1/15 first, then the 14th, 13th and so on all the way to the 9th. There could be as many as 20 names per day (line), and I also need help getting past hte null cells. Sheet two will have a start and end date listed on the page. Week ending: January 15, 2006 Smith Jones Cooper Smith Cooper Smith Jones Smith Jones Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if, sumproduct, help by date
So what is the formula?
"Biff" wrote: Hi! Your bottom "chart" doesn't seem to jive with your explanation and the data posted in the top "chart". Based on my understanding, wouldn't it be: Week ending: January 15, 2006 15 = Smith Jones Cooper Smith 14 = Cooper Smith 13 = Jones 12 = Smith Jones Cooper Smith 11 = Jones 9 = Jones Cooper This is fairly easy to do but requires "complicated formulas" that may effect the overall performance of your file. How big of a dataset has to be searched on Sheet1 in order to extract the required data? If there may be up to 20 names per day, then you'll need 20*7 formulas. That's not a lot if this is all your file is doing. Biff "Jim" wrote in message ... Hello, This first chart is where I am entering names by day (sheet one). Please notice that not all fields are filled in (this will vary by day). Name 1 Name 2 Name 3 Name 4 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006 Smith Jones Cooper 1/6/2006 Smith Jones 1/7/2006 Jones Cooper 1/8/2006 1/9/2006 Jones Cooper 1/10/2006 1/11/2006 Jones 1/12/2006 Smith Jones Cooper Smith 1/13/2006 Jones 1/14/2006 Cooper Smith 1/15/2006 Smith Jones Cooper Smith 1/16/2006 What I need help with is writing a formula that will bring the names from sheet 1 into a table I will keep on sheet 2. The names need to come over by weekending: for example in the chart below I need to have the names listed on 1/15 first, then the 14th, 13th and so on all the way to the 9th. There could be as many as 20 names per day (line), and I also need help getting past hte null cells. Sheet two will have a start and end date listed on the page. Week ending: January 15, 2006 Smith Jones Cooper Smith Cooper Smith Jones Smith Jones Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if, sumproduct, help by date
After tinkering around with this it's not as complicated as I first thought.
Here's a sample file: http://s57.yousendit.com/d.aspx?id=0...D2OAYNMC3WX7B3 The file is based on your posted sample. If you actually have up to 20 names per date then all you need to do is increase the range sizes and copy the formulas to cover that range. Biff "Jim" wrote in message ... So what is the formula? "Biff" wrote: Hi! Your bottom "chart" doesn't seem to jive with your explanation and the data posted in the top "chart". Based on my understanding, wouldn't it be: Week ending: January 15, 2006 15 = Smith Jones Cooper Smith 14 = Cooper Smith 13 = Jones 12 = Smith Jones Cooper Smith 11 = Jones 9 = Jones Cooper This is fairly easy to do but requires "complicated formulas" that may effect the overall performance of your file. How big of a dataset has to be searched on Sheet1 in order to extract the required data? If there may be up to 20 names per day, then you'll need 20*7 formulas. That's not a lot if this is all your file is doing. Biff "Jim" wrote in message ... Hello, This first chart is where I am entering names by day (sheet one). Please notice that not all fields are filled in (this will vary by day). Name 1 Name 2 Name 3 Name 4 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006 Smith Jones Cooper 1/6/2006 Smith Jones 1/7/2006 Jones Cooper 1/8/2006 1/9/2006 Jones Cooper 1/10/2006 1/11/2006 Jones 1/12/2006 Smith Jones Cooper Smith 1/13/2006 Jones 1/14/2006 Cooper Smith 1/15/2006 Smith Jones Cooper Smith 1/16/2006 What I need help with is writing a formula that will bring the names from sheet 1 into a table I will keep on sheet 2. The names need to come over by weekending: for example in the chart below I need to have the names listed on 1/15 first, then the 14th, 13th and so on all the way to the 9th. There could be as many as 20 names per day (line), and I also need help getting past hte null cells. Sheet two will have a start and end date listed on the page. Week ending: January 15, 2006 Smith Jones Cooper Smith Cooper Smith Jones Smith Jones Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| Using sumproduct to count number by date | Excel Worksheet Functions | |||
| Date Math Problem | Excel Worksheet Functions | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Linear Mode
