Named range expansion by date??

[Simon Lloyd]

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

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Simon Lloyd]

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

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Simon Lloyd]

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