Hi all,

Is it possible to have a named range that stays between two dates that
you have in a column? In column A i have all the dates of the year, all
the other columns have named ranges spanning the year in 3 sections i.e
1/1/06 - 14/4/06 is Hols1 15/4/06 - 08/10/06 is Hols2 etc. My trouble
is when a line is inserted into one of the ranges lets say 16/1/06 (not
that is the date causing a problem) the range expands as it should but
it encroaches on the next range by pushing it down the sheet, so the
next named range will actually start one row further down.

is there any way of getting the named range to stay within the dates?

I hope i explained it well enough but if not let me know!

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=499325

Simon,

I am not sure I get the problem.

I assume that Hols 1 is a range that included those dates between 1/1 and
14/4 that are holidays, 3 or 4 dates say. Similarly Hols2. But where is
Hols1 and Hols2, columns B and C, or all in B, or what? And are you meaning
that you are adding another date to Hols1 that causes the problem?

BTW, are Hols1,2 etc static ranges or dynamic?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Simon Lloyd"
wrote in message
...

Hi all,

Is it possible to have a named range that stays between two dates that
you have in a column? In column A i have all the dates of the year, all
the other columns have named ranges spanning the year in 3 sections i.e
1/1/06 - 14/4/06 is Hols1 15/4/06 - 08/10/06 is Hols2 etc. My trouble
is when a line is inserted into one of the ranges lets say 16/1/06 (not
that is the date causing a problem) the range expands as it should but
it encroaches on the next range by pushing it down the sheet, so the
next named range will actually start one row further down.

is there any way of getting the named range to stay within the dates?

I hope i explained it well enough but if not let me know!

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=499325

Hi Bob thanks for replying!

The ranges as i have named them is shown below, on a sheet that counts
the instances of a name in these cells i have this formula (which
varies of course depending whose name its near, i.e
=COUNTIF(Hols1,H2)*8+COUNTIF(Hols2,H2)*12+COUNTIF( hols3,H2)*8+F2)),
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( hols3,E2)*8+F2

The formula just multiplies the instance by either 8 or 12, if in
between the dates which are all in column A another row is inserted
(because we may need more than one instance of any one date) the above
calculations go out slightly, so if i added a row in lets say somewhere
in Hols1 any thing below it will be shoved along by one, so
theoretically what should count as an 8 will count as a 12 and vice
versa, which means each time a date is added i manually have to reset
the ranges to achieve the correct calculations.

=Holidays!$D$14:$AK$132 = Hols1
=Holidays!$D$133:$AK$348 = Hols2
=Holidays!$D$349:$AK$440 = Hols3

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=499325

Well I have no idea why you wouldn't want the ranges to shift down, but
notwithstanding that, perhaps this will help.

Define Hols 1 as

=INDIRECT("Holidays!$D$14:$AK$132 ")

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Simon Lloyd"
wrote in message
...

Hi Bob thanks for replying!

The ranges as i have named them is shown below, on a sheet that counts
the instances of a name in these cells i have this formula (which
varies of course depending whose name its near, i.e
=COUNTIF(Hols1,H2)*8+COUNTIF(Hols2,H2)*12+COUNTIF( hols3,H2)*8+F2)),
=COUNTIF(Hols1,E2)*8+COUNTIF(Hols2,E2)*12+COUNTIF( hols3,E2)*8+F2

The formula just multiplies the instance by either 8 or 12, if in
between the dates which are all in column A another row is inserted
(because we may need more than one instance of any one date) the above
calculations go out slightly, so if i added a row in lets say somewhere
in Hols1 any thing below it will be shoved along by one, so
theoretically what should count as an 8 will count as a 12 and vice
versa, which means each time a date is added i manually have to reset
the ranges to achieve the correct calculations.

=Holidays!$D$14:$AK$132 = Hols1
=Holidays!$D$133:$AK$348 = Hols2
=Holidays!$D$349:$AK$440 = Hols3

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=499325

Thanks Bob, but its because the calculations are changing because if
have defined hols1 as eg D1:K20 and insert a line the range should no
expand to D1:K21 but its staying as D1:K20 so the last row has moved ou
of the area that is x8 into the area thats x12, if it could make th
range expand to D1:K21 and each of the others still cover the date
they were ranged for until they have one added and their range expan
down by one......i very much doubt im being clear with my explanation.
If you would like to see the workbook and what i have done with it you
more than welcome.

Thanks,

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=49932