First, I would probably set up a dyanamic named range for the entire data
set. I'm assuming your dates are on Sheet1, begin in cell A1, and there's no
other extraneous data in Column A (below your table-otherwise this messes
with the COUNT function). Click Insert/Names/Define
EntireRange = OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),1)
Then, on an empty worksheet (say Sheet2), I'd set up a table to find the
index numbers of the first and last cell within your range (first cell =
1/1/06 and the last cell <=5/5/06).
A B
1/1/06
=MIN(IF(EntireRange=A1,(EntireRange=A1)*(ROW(IND IRECT("1:"&ROWS(EntireRange)))),""))
5/5/06
=MAX(IF(EntireRange<=A2,(EntireRange=A2)*(ROW(IND IRECT("1:"&ROWS(EntireRange)))),""))
These are array formulas, so after typing them in, hit Control+Shift+Enter.
If done properly, you should see curly brackets { } around the formula. I
assume your dates are sorted in ascending order, otherwise you could use the
MATCH function to get this info.
Then, I'd set up another dynamic named range for the subrange,
1/1/06-5/5/06. Click Insert/Names/Define
SubRange = OFFSET(Sheet1!$A$1,Sheet2!$B$1-1,0,Sheet2!$B$2-Sheet2!$B$1+1,1)
You can check what the subrange is by entering =Index(SubRange,1) in an
empty cell. This will give you the first item in the subrange - change the
cell format to date.
Does this help any?
"Simon Lloyd" wrote:
Hi all,
Does anyopne know how to keep a named range between two dates?, i have
a couple of named ranges and for one reason or another interfere with
each other when new rows are added, as the new rows that are added
contain a date and are sorted by date i was wondering if i could
restrict the named ranges to operate between certain dates i.e 1/1/06 -
5/5/06 could be named range 1 etc for all 3 named ranges i have.
Anyone got any ideas?
cheers,
Simon
--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=511571