| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Picking out a Period, in relation to certain cells
I need a formula that will display which period a set of dates belongs to.
The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
=INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period
Dates'!C2:C14=T$),0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vikki1603" wrote in message ... I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
Hi Vikki,
copy the A column (Period) to column D, (assuming you have A=Period, B=Start and C=End), than use a Vlookup function as: =vlookup(e2,b2:d20,3,1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "vikki1603" escreveu: I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
This formula doesnt seem to be working, its giving a #VALUE! error??
"Bob Phillips" wrote: =INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period Dates'!C2:C14=T$),0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vikki1603" wrote in message ... I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
Thanks Marcelo, but I cannot use a vlookup as it is someone elses sheet so I
cannot move the columns about. I also need to find whether the date is earlier or later than in the two 'start' and 'end' columns, and I cannot do that with a vlookup. "Marcelo" wrote: Hi Vikki, copy the A column (Period) to column D, (assuming you have A=Period, B=Start and C=End), than use a Vlookup function as: =vlookup(e2,b2:d20,3,1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "vikki1603" escreveu: I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
I beleive you need to enter Bob's formula as an Array, meaning you commit it
with the Ctrl-Shift-Enter keys, all at once. "vikki1603" wrote: This formula doesnt seem to be working, its giving a #VALUE! error?? "Bob Phillips" wrote: =INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period Dates'!C2:C14=T$),0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vikki1603" wrote in message ... I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Picking out a Period, in relation to certain cells
Wow, that is brilliant. Thankyou both...
"Duke Carey" wrote: I beleive you need to enter Bob's formula as an Array, meaning you commit it with the Ctrl-Shift-Enter keys, all at once. "vikki1603" wrote: This formula doesnt seem to be working, its giving a #VALUE! error?? "Bob Phillips" wrote: =INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period Dates'!C2:C14=T$),0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vikki1603" wrote in message ... I need a formula that will display which period a set of dates belongs to. The info looks like this, located in a sheet named 'Period Dates'. Period Start End Period 1 01/04/2006 28/04/2006 Period 2 29/04/2006 26/05/2006 Period 3 27/05/2006 23/06/2006 Period 4 24/06/2006 21/07/2006 Period 5 22/07/2006 18/08/2006 Period 6 19/08/2006 15/09/2006 Period 7 16/09/2006 13/10/2006 Period 8 14/10/2006 10/11/2006 Period 9 11/11/2006 08/12/2006 Period 10 09/12/2006 05/01/2007 Period 11 06/01/2007 02/02/2007 Period 12 03/02/2007 02/03/2007 Period 13 03/03/2007 30/03/2007 In another sheet, I have column T called 'date in'. I need the formula to pick out this date, find it in the info above and return the corresponding period. However, it is proving difficult as the date is located between the 'start' and 'end' dates. I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period 6"). I can only do up to 8 IF statements though, is there another option?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) | |||
| Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
| Help adding text values | Excel Worksheet Functions | |||
| Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
Linear Mode
