Formular to Calculate if a name has been used

[Por2gal]

I have read up on data validation process, however it is lost to me due to a different scenario.

I wish to get some help to 'Calculate if a name has been used' and how to limit the choices in a drop down list, hiding items that have been previously selected? i.e I am assigning employees to a vehicle number, I don't want to assign the same employee twice.
I have a table with 3 lists
one list consists of names the other list consists of different numbered vehicles, whilst the 3rd list whether it has been used (yes/no)
a list of 30 vehicles with inconsistent numbers i.e starting at # 006 to 1014 but not in any order or in sequence.

can I get some help with a formular to calculate this in Excel.
PLEASE

[jason.b75]

There may be other methods, but this is one way to do it. (Adjust the ranges to suit your actual layout).

Enter your list of names in A2:A10

Enter this Array formula into B2 (the range $E$2:$E$10 refers to the range that will hold the data validation dropdowns).

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISERROR(MATCH($A$2:$A$10,$E$2:$E$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($B$2:$B2))),"")

Note that Array Formulas must be confirmed by pressing Ctrl Shift Enter, not just Enter.

Once the array is confirmed, fill the formula down parallel to the original list of names.

Finally, set up your data validation using =$B$2:$B$10 as the source for the list.

As you select names in the dropdowns, they should go from the list in column B.

This is a general example based on your post, if this doesn't help sufficiently then it would be benificial for you to attach a copy of your workbook so that we can see the actual layout.

Please attach an actual workbook, not a picture of one, remembering to fictionalise any personal / confidential details first.
Please add some comments to the workbook to show the locations of the relevant information.

To attach a workbook to your post, click the 'Go Advanced' button in the bottom right corner of the post editor, then scroll down a little and look for the 'Manage Attachments' link under the Attachments heading.

[Por2gal]

thanking you in advance for your help in this matter
I have attached my workbook with the data already in place, however I am having difficulty in validating names that have already been in use for the column. each column is equal to the previous column and set in 3 day slots.
however when choosing a name in the next 'row', I want to hide the name from the previous row, if it has already been used.
the 'yes/no' data stays the same throughout, so this will not need to be addressed.

[FlameRetired]

If I understand correctly this is as good as I can come up with. The formula for the diminishing DV is on the long side. Perhaps someone will come up with a simpler/shorter solution.

Array entered in M1 of 'data validation' and filled down until you get blanks. Done in the attached.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then the DV formula for column C, E and G

Please Login or Register  to view this content.

This limits the DV drop down to names that have not been used anywhere in columns C, E and G.

[Por2gal]

thanx Dave,
you are almost there, the names disappear in column 'c', which is what I wanted, however I would like the names to reappear in column 'e' and 'g' due to several of the same drivers are driving different vehicles in the one week, then disappear down the column. this data sheet is updated on a weekly basis.

[FlameRetired]

OK. It sounds like you will need 3 separate DV lists for that. Are you OK with that?

[Por2gal]

thank you yes,

[FlameRetired]

In the attached array entered in M1, N1 and O1 respectively and filled down until you get blanks. These are the 3 separate DV lists.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Then the three separate DV definitions columns C, E and G respectively.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Por2gal]

THANK YOU FlameRetired. I now understand the sequence of events from the worksheet you've shown me. thank you again, its taken me a year to work a solution. Happy New Year to you.

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback and added rep.

One more thing:

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jason.b75]

If I understand correctly this is as good as I can come up with. The formula for the diminishing DV is on the long side. Perhaps someone will come up with a simpler/shorter solution.

I don't think that there is a shorter / simpler method, Dave.

The only thing I would have done slightly differently would be to use ROW(Table7[#Headers]) instead of MIN(ROW(Table7[NAMES]))+1

But that's not an improvement, just a personal preference.

[FlameRetired]

Thank you Jason. Structured table references make for annoyingly long formulas. Any help in shrinking those is welcome.

Dave