Conditional formatting - highlight multiple cells based on matching values in adjacent row

Hi

Hope someone can help. I've searched multiple forums but couldn't find an answer.

I have a table with text entries (e.g., A, B, C, D, E, etc) in a column. In an 'out of the way' part of the spreadsheet against each column value there are a list of values in a row, e.g., for A the values in the row are B, C, D. For B they are A, F, G, etc.

What I would like to do is to use conditional formatting (not VBA) and if a cell in the table contains an "X" to check the row values (in the 'out of the way part') and highlight the cells in its column that are adjacent to the same row values.

I have uploaded a spreadsheet on how I'd like this to work.

Many thanks in advance.

Never tried office 365 but in other versions I would use the following:

Select the range for the conditional formatting (in your example F2:H7). In conditional formatting select Use a formula to determine which cells to format, then add the following =B2="X" in the Format values where this formula is true.
Then format the colour you want.

Hope this helps

Thanks for your response Windy but I don't think that will completely solve my issue..

I think it'll need something adding to the formula so it can pick out the cells that have been referenced in the "out of the way part of the spreadsheet". Did you manage to download the dummy spreadsheet I uploaded - hopefully it will help better explain my problem.

Cheers

Have attached copy of your spreadsheet so hopefully you can see what I have done.

Thanks for trying Windy, but I may have not explained myself very well. The cells that I need highlighting are the ones in yellow highlight that I have applied manually in the spreadsheet. This should be driven by the 'X' in the cell for that column and by checking the letters in the same row as the 'X', those letters are the ones that need to be highlighted.

For example, in the Person 1 column, row A has the 'X', and for that row, the following cells need to be highlighted as per the "out of the way part of the spreadsheet", which are for rows B, C and D (for Person 1).

For Person 2, the 'X' is in row D. For row D, rows A and rows E need to be highlighted as per the "out of the way part of the spreadsheet" for Person 2.

Etc.

Hope this makes more sense?

Thanks