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]...

 

[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]...

 

[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