Keeping a named range between two dates?
 

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

Keeping a named range between two dates?
 

Possibly if you made the defined name dynamic using a formula which
determines the extent and location of the named range based on the dates.

--
Regards,
Tom Ogilvy





"Simon Lloyd"
wrote in message
...

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

Keeping a named range between two dates?
 

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

Keeping a named range between two dates?
 

Tom, JMB,

Thanks for replying, i can perform fairly routine vba programming but
any more than simple is a bit beyond me. To get my named range i
highlighted the area i wanted and then in the window next to the
formula bar gave it a name, the first range is called Hols1 and this
=Holidays!$A$14:$AK$133 is the range, i use the ranges like this
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 on
another sheet where hols2 & 3 represent the other 2 portions of the
year.

So i would like know how to change the "hols" for something where it
would only count if the value of E2 is found between and including 2
dates i.e count for as many times if "eric" (the value of E2) is found
between 1/1/06 & 15/4/06 inclusive and so on...

the countif stuff i did above seemed fairly simple, but the rest is
beyond me!

regards,

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

Keeping a named range between two dates?
 

So adjusting the named range is not the end result you want? Sounds like you
want a multiple condition test.

Assuming that your table looks like:

A B
12/31/05
1/1/2006 eric
2/1/2006 joe
4/15/2006 eric
5/15/2006 eric
3/15/2007 bob


and cell E2 = eric


=SUMPRODUCT(--(B1:B6=E2),(A1:A6=DATE(2006,1,1))-(A1:A6DATE(2006,4,15)))

would give you 2. With this formula, it doesn't matter if the data is
unsorted.

With Sumproduct, you cannot use entire columns (such as A:A, but A1:A65535
works fine - unless you're on an old version of excel that has fewer than
65536 rows).






"Simon Lloyd" wrote:


Tom, JMB,

Thanks for replying, i can perform fairly routine vba programming but
any more than simple is a bit beyond me. To get my named range i
highlighted the area i wanted and then in the window next to the
formula bar gave it a name, the first range is called Hols1 and this
=Holidays!$A$14:$AK$133 is the range, i use the ranges like this
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 on
another sheet where hols2 & 3 represent the other 2 portions of the
year.

So i would like know how to change the "hols" for something where it
would only count if the value of E2 is found between and including 2
dates i.e count for as many times if "eric" (the value of E2) is found
between 1/1/06 & 15/4/06 inclusive and so on...

the countif stuff i did above seemed fairly simple, but the rest is
beyond me!

regards,

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

Keeping a named range between two dates?
 

JMB, again thanks for replying, i understand the formula you gave and
can see it will work fine for counting instances of the value in
whichever cell the test is in, but in the formula i use
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 i
need to multiply each instance by either 8 or 12 depending where it
finds the instance (this is because it is counting up the amount of
hours someone has booked off), i can of course mail you the workbook if
you would like to see what i'm getting at!. I am using excel 2003 and
win xp.

My e-mail address is simonwlloydATmsnDOTcom, if you want to mail me
direct i can send you the workbook by return.

Thanks,

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