Luke,

I notice your formula works great however it seems to be dependent on me
updating all my worksheets (18 Jan, 25 Jan etc).

Is there no way around this as this would be a real bug bear when I get a
new employee half way through the year and I have to adjust all worksheets
and not just the ones he would be inserted into.

Many thanks



"Pootle" wrote:

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.