Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range expansion by date??
Hi all, Anyone know how to get a named range to expand and contract to keep it within dates in column A?, i have 3 named ranges on a w/s the first spans dates 1/1/06 - 14/4/06 and the other 2 are segmented for the rest of the year, when i insert another row/second occurance of a date in this range the named range stays for the range set and the last row of the range is pushed down i.e if 14/4/06 was row 123 then it will now be 124 but the named range still only covers rows 1 - 123. This is why i would like the named range to expand to cover the added row/date or contract if i remove a row/date. Any ideas? All help appreciated, 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=504473 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range expansion by date??
As long as you insert rows within the boundaries of the named range, it
should expand just like any other formula would. How are you defining the range/name. -- Regards, Tom Ogilvy "Simon Lloyd" wrote in message ... Hi all, Anyone know how to get a named range to expand and contract to keep it within dates in column A?, i have 3 named ranges on a w/s the first spans dates 1/1/06 - 14/4/06 and the other 2 are segmented for the rest of the year, when i insert another row/second occurance of a date in this range the named range stays for the range set and the last row of the range is pushed down i.e if 14/4/06 was row 123 then it will now be 124 but the named range still only covers rows 1 - 123. This is why i would like the named range to expand to cover the added row/date or contract if i remove a row/date. Any ideas? All help appreciated, 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=504473 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range expansion by date??
Thanks for replying, i'm naming the ranges Hols1, Hols2 and Hols3 in this format =Holidays!$A$14:$AK$131 (this one is Hols1, the other two come directly below this one i.e =Holidays!$A$132:$AK$232 etc.), when a line is taken off the bottom of all these ranges and because of a sort by date in Auto_close and inserted into one of these ranges my count up worksheet goes all to pot because of the description i gave in the first post. my count up sheet and formulas work fine, its just beacuse its referencing a named range that is not expanding as it should, i could mail you the workbook if you would like to see whats happening. My e-mail simonwlloydATmsnDOTcom Hope you can help, 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=504473 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range expansion by date??
As I understand the description you have now given, you are entering a new
row of data at the very bottom of the data, then sorting the data to get it into the correct position. If that is what you are doing, then there is no reason to expect excel to change the defined name. Inserting data usually refers to selecting a row, doing Insert=Row. In that case, the ranges (such as you define them) will adjust (regardless of what is entered in the space). Your defined name has no understanding of the fact that it marks ranges of dates - so when you sort in a date, there is no adjustment. To achieve expansion under those conditions, you would need to build a formula that determines the extent of the range based on the values in the cells. -- Regards, Tom Ogilvy "Simon Lloyd" wrote in message ... Thanks for replying, i'm naming the ranges Hols1, Hols2 and Hols3 in this format =Holidays!$A$14:$AK$131 (this one is Hols1, the other two come directly below this one i.e =Holidays!$A$132:$AK$232 etc.), when a line is taken off the bottom of all these ranges and because of a sort by date in Auto_close and inserted into one of these ranges my count up worksheet goes all to pot because of the description i gave in the first post. my count up sheet and formulas work fine, its just beacuse its referencing a named range that is not expanding as it should, i could mail you the workbook if you would like to see whats happening. My e-mail simonwlloydATmsnDOTcom Hope you can help, 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=504473 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range expansion by date??
Tom, Thanks again for replying, I have no idea how to build such a formula the formula on my count up sheet is this =COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( Hols3,E2)*8+F2 (although it does vary from cell to cell as it looks for names on another sheet but it still looks for the named ranges) E2 shown here is the name it is looking for in the named range (formula stored in G2) so in the next cell down it will look at E3 etc. its when i sort a row in to the named ranges that it no longer counts properly i.e instead of counting in multiples of 8 it may count in 12's or vice versa! Any ideas? 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=504473 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
Do Pivot Tables have an automatic data range expansion? | Excel Discussion (Misc queries) |