Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keeping dates in cells hidden | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Keeping a named range between 2 dates?? | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
keeping dates in UK format | Excel Discussion (Misc queries) |