Formula for conditional formatting - using part of the criteria from another cell.

[daren.beaney]

Hi, I'm really struggling with how to solve a problem I have. What I have is:

1. A spreadsheet that contains different rugby clubs per age group.
2. A defined drop-down list of those clubs in cell D3
3. Conditional formatting to highlight the clubs based on the drop-down value selected

The situation has now changed and I have [club name] A or B What I need to do now is:

1. Use the same process above but highlight the club regardless of it having A or B on the end.

So by selecting say a club name like Ashford via the drop-down in D3 it would highlight any cell that had either Ashford A or Ashford B within cells C6:H32.

Hope that makes sense.

Thanks for your help.

 

[StephenCrump]

Try conditionally formatting C6:H32 with the formula:

=LEFT(C6,LEN($D$3))=$D$3

This assumes that there is no overlap of team names, e.g. if you have another team called Ashford North, or Ashfordworth, say, we'll need to change the approach.