[Closed] Excel question

try and explain your problem again/

conditional formatting will do what you want, but I need you to explain "cells in between " and "who have appear in more than of the programmes listed in the other columns?" to help with the syntax.

Conditional Formatting - on the Home tab in Excel 2007-2010.
Under the Format menu in previous versions.

Column B rows 4-11000 is people's names

Row 1 columns c-AFX is TV programmes

The cells in between C4-AFX11000 are mainly empty except where there is a match between a person and tv prog and this tells me what their job was on that programme, e.g. person in B16 worked on Byker grover (column D), so in D16 there is the word 'director'

In some cases people worked on two or more programmes e.g. person in B28 who worked on byker grove as script editor (D28) and 50 degrees north (column H) as a script supervisor (written in H28).

I want to highlight people in column B who have any kind of text in more than one cell between C4-AFX11000

is that clearer?

i'm unclear what to enter in the conditional formatting bit

May be a case for the use of the cunning sumproduct function here - more info required, though, best if you can supply a workbook with a sample of the data.

wow, is the final column details of from whereabouts in their gardens you get the best view of the bathroon ?

Select column B
Format>Conditional formatting
Change drop down box to "Formula is"
in dialogue box to right, paste "=((COLUMNS(C4:AFX4)-COUNTIF(C4:AFX4,""))>1)
Set format>patterns to desired colour.
OK

That has turned everything yellow... thanks though, i will have a play with that formula.

sorry, I mean select B4 then do the conditional format.

then use the format painter to take the format down the B column.

oh, i think that may have worked! thank you.

BTW to make it more useful, use the same formula in a new column to the left of B (dont forget to chaneg the column reference in the formula to take into account he inserted column) but turn it into an IF statement with a 1/0 response, Then you can sort the list to group those with more than one role. YOu can also link the ">1" in the formula to an input cell where you can set the "How many programmes "threshold, so you can find "more than three" or "more than 10"

like this in your new B4 (having inserted a new column at B)

=IF((COLUMNS(D4:AFX4)-COUNTIF(D4:AFX4)>A2, 1, 0))

where A2 is a number from 0 to say 10

then use data>sort on column B to group returns of "1"

Cool.