Data collation

[Pootle]

Hi,

I have weekly attendance registers in individual worksheets that display
whether a person was "in", "sick", "u/a" or "hols" from Sun-Sat.

I have adapted a formula;

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

from these forums that works to a degree however, I have an issue with new
employees. I cannot find a way to update the sheet so that the new employee
can slot in alphabetically and not upset the above formula as he would then
take over the range of b5:h5 that is meant for someone else.

Any help would be greatly appreciated.

Thanks

Paul

 

[Shane Devenshire]

Hi,

I'm not sure what you are doing with this formula, but you could try

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

That is removing the "" around the cell address portion.

Just a comment - this doesn't look like a very flexible formula, I would
consider using cell refereces in place of "18 Jan" and 25 Jan if possible.

 

[Pootle]

Hi

The 18 Jan is the name of the worksheet. I will obviously have 52 worksheets
by the end of the year.

I have 66 employees listed down and the days of the week across in the 18
Jan and the rest of the weeks worksheets that will be added. I am trying to
keep a summary sheet that tells me how many days holiday J.Bloggs has left,
how many sick days he has had so far etc etc by counting the text across all
the 'week' worksheets for that particular person.

It's not really practical for me to use cell references due to the amount of
data being held and the nature of it. I still need to hold the data
week-by-week as other depts use it.

Your code seems to have a circular reference problem it seems to be
searching in b5:h5 of the same worksheet.

Regards

Paul