Thanks for your responses.

The reason I wouldn't want to use Fred's example is because I want to
continually use this so would end up with 52 sheets for 52 weeks of the year
and thus still have another 50 additions to the formula.

"Fred Smith" wrote:

One of the reasons for using Indirect is so that references *don't* change.
As you have only two conditions, one solution would be:

=countif('18 jan'!b10:h10,"hols")+countif('25 jan'!b10:h10,"hols")

Regards,
Fred.

"Pootle" wrote in message
...
Hi

I have employee time sheets with a list of names with formulas attached to
adjacent cells to count holidays and sick. I want to know if there is a
way
to insert a row so I could add a new name but also so the formula follows
the
name.

The formula I have is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25 jan"}&"'!b10:h10"),"hols"))

So I need the b10:h10 to move down automatically to b11:h11.

Any ideas greatly appreciated.