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