Named range expansion by date??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Lloyd - 24 Jan 2006 14:45 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 24 Jan 2006 15:02 GMT

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.

Signature

Regards,
Tom Ogilvy

> Hi all,
> Anyone know how to get a named range to expand and contract to keep it
[quoted text clipped - 14 lines]
> thanks
> Simon

Reply to this Message

Simon Lloyd - 24 Jan 2006 22:14 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 25 Jan 2006 03:12 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 12 lines]
> Hope you can help,
> Simon

Reply to this Message

Simon Lloyd - 27 Jan 2006 10:46 GMT

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

Signature

Simon Lloyd

Reply to this Message