data validation and conditional formatting

Hi all,

I must start by saying I'm fairly new to excel and I have started creating a workbook with multiple sheets. Here is what I'm trying to do:

I have a list of NHL players on one sheet. I have called this sheet called Forwards. I have another sheet I have called Line Up. The Line Up sheet has a data validation list of all the forwards from the Forwards sheet. When a player is selected from the data validation list on the the My Picks sheet, I would like their name removed or even highlighted a different colour from the Forwards sheet to indicate that they have already been selected.

is this possible. I'm going nuts trying to figure this out.

Thanks for the help,
Chris

hi
first: you insert this formula in cell M2:

Please Login or Register  to view this content.

the you drag down to the end
second: you go to conditional format and insert this formula:

Please Login or Register  to view this content.

and you can format the cells as you like
in our example I make the cells red with strikethrough
finally: you can hide column M
by this way, when you select any player in sheet "line up"
then the cell of the same player in sheet "forwards" will be highlighted
attached the file

Hi Chris

Please find attached a revised version of your example that removes items from the list as they are chosen. You will see that there are two columns on the forwards page that are used to allow this to happen, these should be hidden when in use. This is currently only for forwards, but can easily be extended to the other sections if it is what you are looking for.

Regards

Jeff

And here is another take on it

You can use Col O as your source for the DV list