Excel - help with staff rota

  InXP 19:29 21 Nov 03

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 03

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 03

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 03

bump up to the top

  InXP 22:11 21 Nov 03

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 03

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

  VoG II 22:24 21 Nov 03

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 03

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

of course.

I do not know if this will work.

  TommyRed 22:30 21 Nov 03

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 03

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.


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.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…