Excel - help with staff rota

  InXP 19:29 21 Nov 2003
Locked

I'm doing a staff rota for a department with 5 sections. There are 32 individuals and each must not appear in more than one section at any one time (on one line). Can excel check and highlight any person appearing more than once, perhaps using an array formula?

  TommyRed 21:06 21 Nov 2003

I'm not sure that MS access wouldn't be better for that, do you have it. It's ages since I used it but any database should suffice. HTH TR

  VoG II 21:24 21 Nov 2003

You could use Conditional Formatting on the Format menu.

Select Formula and in the formula box enter e.g.

=COUNTIF(A1:A100, "Joe Bloggs") > 1

Then select Red colour.

  TommyRed 22:09 21 Nov 2003

bump up to the top

  InXP 22:11 21 Nov 2003

Thanks VoG. It works if you enter each name individually. Can this be built into an array formula, to check each name in turn?

  InXP 22:22 21 Nov 2003

TommyRed, I have got Access but I've never used it before.

  VoG II 22:24 21 Nov 2003

I have to be honest and say "I don't know". But I do not think that you can use Array Formulas within conditional formatting.

Try (as a Conditional Format)

= SUMPRODUCT( (A1:A100) * (B1*B100)) > 1

  VoG II 22:25 21 Nov 2003

= SUMPRODUCT( (A1:A100) * (B1:B100)) > 1

of course.

I do not know if this will work.

  TommyRed 22:30 21 Nov 2003

No, I'm not much good at it either (so the wife says) but if we keep you up here at the top maybe someone will come up with a solution. If you see your post slipping down the list just add a 'bump' to move it back up top. TR

  InXP 20:01 22 Nov 2003

Thanks VoG and TommyRed. I've cracked it, after modifying VoG's formula. I set up the rota in rows, on a worksheet called 'rota' and I put the list of names on a worksheet called 'check'. I assigned a name (names) to the range containing the list of names. On the sheet called 'check', I entered the formula in the first cell corresponding to the first cell of the rota, then autofilled.

=COUNTIF(rota!$A1:$I1,names)

If a name occurs twice I get the answer 2, 1 for once and 0 if a name is missing altogether.

Thanks again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?