Inserting a row

[Pootle]

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.

 

[Eduardo]

Hi,
Go to the left margin of the spreadsheet and click on the row where you want
to insert, do insert, it will insert a new whole row, copy the formulas from
above and you are done

If this was helpful, please say yes. Thank you

 

[Fred Smith]

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.

 

[Luke M]

You could slightly modify formula to:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"18 jan","25
jan"}&"'!B"&ROW(A10)&":H"&ROW(A10)),"hols"))

Now you have cell references determining the row number, and they will
adjust accordingly.

 

[Pootle]

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.

 

[Pootle]

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

 

[Luke M]

As long as it follows a pattern or you have the info located somewhere, you
can automate it. But, you didn't say what you wanted the dates to change to.

I'm not sure I understand what you're exactly trying to do. It sounds like
you're trying to do a 3D reference, but neither COUNTIF nor INDIRECT support
such a thing. If you could describe in more detail what your sheet layout
looks like, "we'll" probably be able to come up with a solution.

 

[Pootle]

I will have 52 worksheets for 52 weeks of the year. I will have another
'summary' sheet where the formula you helped me with is seated.

In the 52 worksheets is a list of employees and across the top is the day of
the week. This is designed to record whether an employee was in, sick or on
holiday.

The idea of the summary sheet is to keep a running count of each employee's
sick and holidays during the year. So as I fill in the worksheet through the
week it is automatically counted on my summary sheet.

I hope this makes sense.

Name Sun Mon Tues Weds Thurs Fri

J.Bloggs in hol hol hol hol sick

So on my summary sheet I will have my formulas counting 4 holidays and 1
sick day for J.Bloggs.

Cheers

Paul