data validation with condition

[Tortus]

I would like to create a cool line up sheet/player position list.

I have a data range named "Field1". In this range of cells I have validation boxes laid out in field positions.

1 cell below each validation box is the initials of the field position. (This could work for any field lay out...baseball,basketball court,football.etc..)

In column B is where the players first name is located and is the validation boxes list. Range name "FirstN"

What I would like to do is when I use the drop down list and pick a name, the position goes in Column E on the same row as the persons name in column C and at the same time strike through the name in column C to identify the player has been placed in a position.

[AliGW]

Attach what you've got so far.

[Tortus]

[AliGW]

To attach a sample workbook:

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tortus]

ok, I've added the link!

[AliGW]

Where have you added it???

[Tortus]

Sorry! Now it is there.

[jason.b75]

Where have you added it???

Post #3, Ali.

@Tortus, could you edit your thread title to something more meaningful in line with the forum rules please. Not following exactly what you're looking for yet, but something like 'data validation with condition' should keep the hounds at bay even if it's not strictly accurate

[AliGW]

Ah, yes - spotted it now. Unfortunately, I'm just about to log off for the evening. Maybe have a look tomorrow.

[Tortus]

You are correct.. I used what you have suggested.

[jason.b75]

First part, in E16 and copied down

=IFERROR(INDEX($J$2:$AU$41,SUMPRODUCT(($J$1:$AU$40=$C16)*ROW($J$1:$AU$40)),SUMPRODUCT(($J$1:$AU$40=$C16)*(COLUMN($J$1:$AU$40)-COLUMN($J$2)+1))),"")

Still trying to figure out what you've done with the validation cells in order to clear the used names.

You can't format the dropdown list with a strikethrough font, the only way to eliminate double entries would be to exclude names from the dropdown that have a position listed in column E.

[Tortus]

ok, is it possible to just highlight the ones used in the list? maybe green?

[jason.b75]

Sorry, I should have been a bit more specific with my comment.

It's not just strikethrough that you can't use, you can't apply any kind of formatting to the dropdown list.

Just making a few changes to the vba code you have in the sheet so that it will take care of the validation for you.

[Tortus]

I've got it! If value of E has text - highlight B - E, would that work?

[jason.b75]

You could do that with conditional formatting, using a simple rule

=LEN($E16)>0

Applied to B16:E32

But the highlight would only show in that list, and not in the dropdown, so it would depend how attentive the user is. There would still be nothing to stop them entering a name twice.

[Tortus]

I see what you mean. The condition formatting would work for v1. I'll leave it open for a bit in case someone else has an idea. There is no way to link the Error message box that would warn the user that the name is already used in Field1?

[jason.b75]

See how this works for you.

Right click the 'LineUp' tab, then click 'View Code'

Delete the sorting code that is already there, and replace it with this code (it takes care of the sorting and the validation).

Please Login or Register  to view this content.

The code re-writes the list of names in the validation dropdown on the fly, so it only ever shows unused names.

The only thing that this doesn't allow for is 2 (or more) players with the same first name.

[Tortus]

There is another way but I'm not sure the formula. How do I condition format column E to show msgbox when a 0 is present in a cell?

[jason.b75]

You wouldn't be able to do that with conditional formatting.

The only message box of any kind that can be triggered without vba is the one in data validation, depending on how you set it up.

That would be based on the validation conditions though, and you would need to be able to apply 2 validation conditions to one cell to make it work, which, to the best of my knowledge, is not possible.