supertemp123
New Member
- Joined
- Jan 17, 2013
- Messages
- 3
Do you know how I would do the opposite? Meaning take another control date (this one later than the first one), matching it against the same list (sorted descending), and finding the first date BEFORE the date in question? In other words, or this second number, the period does NOT count the index date that is outside the date range (the range between control date #1 and control date #2) -- the last "list date" counted in the period is the last date BEFORE control date # 2.
The way I attacked the problem was to run the same formula against the list for both dates and find the "union" where date #1 is greater and date #2 is not greater.
=ADDRESS(MAX(IF(NumRange=MAX(NumRange),ROW(NumRange))),COLUMN(NumRange),4)
I did an "IF TRUE" test for this date greater than the other for control #1 and control #2, indicated where the LIST DATE was greater than the control date for both control numbers. From that, I did a concatenate and figured out the UNION of dates where the control date #1 was greater than the list date AND the dates where the control date #2 was less than the list date. The "TRUEFALSE" subset of dates.
LIST DATES: CONTROL DATE #1 -- 10/15/94 (ED -- Early Date) -- List Date is Greater? CONTROL DATE #2 -- 5/17/06 (LD -- Later Date) -- List Date is Greater?
12/1/2012 TRUE TRUE
12/1/2011 TRUE TRUE
12/1/2010 TRUE TRUE
12/1/2009 TRUE TRUE
12/1/2008 TRUE TRUE
12/1/2007 TRUE TRUE
12/1/2006 TRUE TRUE
12/1/2005 TRUE FALSE
12/1/2004 TRUE FALSE
12/1/2003 TRUE FALSE
12/1/2002 TRUE FALSE
12/1/2001 TRUE FALSE
12/1/2000 TRUE FALSE
12/1/1999 TRUE FALSE
12/1/1998 TRUE FALSE
12/1/1997 TRUE FALSE
12/1/1996 TRUE FALSE
12/1/1995 TRUE FALSE
12/1/1994 TRUE FALSE
12/1/1993 FALSE FALSE
12/1/1992 FALSE FALSE
From this "subset" of the greater list, however, I need to somehow indicate on a separate spreadsheet whicheverdates are in this subset .. e.g. A1 is date #1 (here 12/1/95), A2 is date #2 (here 12/1/96) -- ascending or descending doesn't matter -- just need to generate this "report". How do I tell the spreadsheet to "copy" this result to another sheet? Is this a Macro? <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>? I need the report to obviously allow enough space for however many dates there are in the period being examined.
The other column of the report would be the column with the rates -- would this just be a VLOOKUP against the table with dates and rates?
e.g.
12/1/95 8%
12/1/96 9%
12/1/97 4%
I apologize for asking ... I am not the normal person at work who has to do these things. I hope to contribute myself once I have more to offer the community.
Thanks again for all of your help (and JonMo's help).
The way I attacked the problem was to run the same formula against the list for both dates and find the "union" where date #1 is greater and date #2 is not greater.
=ADDRESS(MAX(IF(NumRange=MAX(NumRange),ROW(NumRange))),COLUMN(NumRange),4)
I did an "IF TRUE" test for this date greater than the other for control #1 and control #2, indicated where the LIST DATE was greater than the control date for both control numbers. From that, I did a concatenate and figured out the UNION of dates where the control date #1 was greater than the list date AND the dates where the control date #2 was less than the list date. The "TRUEFALSE" subset of dates.
LIST DATES: CONTROL DATE #1 -- 10/15/94 (ED -- Early Date) -- List Date is Greater? CONTROL DATE #2 -- 5/17/06 (LD -- Later Date) -- List Date is Greater?
12/1/2012 TRUE TRUE
12/1/2011 TRUE TRUE
12/1/2010 TRUE TRUE
12/1/2009 TRUE TRUE
12/1/2008 TRUE TRUE
12/1/2007 TRUE TRUE
12/1/2006 TRUE TRUE
12/1/2005 TRUE FALSE
12/1/2004 TRUE FALSE
12/1/2003 TRUE FALSE
12/1/2002 TRUE FALSE
12/1/2001 TRUE FALSE
12/1/2000 TRUE FALSE
12/1/1999 TRUE FALSE
12/1/1998 TRUE FALSE
12/1/1997 TRUE FALSE
12/1/1996 TRUE FALSE
12/1/1995 TRUE FALSE
12/1/1994 TRUE FALSE
12/1/1993 FALSE FALSE
12/1/1992 FALSE FALSE
From this "subset" of the greater list, however, I need to somehow indicate on a separate spreadsheet whicheverdates are in this subset .. e.g. A1 is date #1 (here 12/1/95), A2 is date #2 (here 12/1/96) -- ascending or descending doesn't matter -- just need to generate this "report". How do I tell the spreadsheet to "copy" this result to another sheet? Is this a Macro? <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>? I need the report to obviously allow enough space for however many dates there are in the period being examined.
The other column of the report would be the column with the rates -- would this just be a VLOOKUP against the table with dates and rates?
e.g.
12/1/95 8%
12/1/96 9%
12/1/97 4%
I apologize for asking ... I am not the normal person at work who has to do these things. I hope to contribute myself once I have more to offer the community.
Thanks again for all of your help (and JonMo's help).