Data Validation - Drop-Down List of Unused Items Only

Hello again, I thought of completing discussion on Data Validation with 5 posts but we have 9 posts so far and I still have material to put forward. Anyway, let’s see another practical example on using Data Validation lists.

Material for today’s post is based on a query I received in one of the Excel online forum, I spent some time answering that. Let me summarise the issue and provide a solution.

Primarily I need to have a drop-down of items across cells in such a way that when I select any one item from the list in a cell, drop-down in other cells should not contain that item any more.

Let’s see it by an example. I have 10 parking slots and I need to allot them to vehicles coming in. Slot once allotted to a vehicle cannot be allotted to another vehicle during the same day. As usual, I am maintaining this in versatile Excel. I am maintaining one sheet with the slot names; this sheet acts as my database. I name the sheet as “Database”. In another sheet named “Allotment”, I have 3 columns namely Date (column A), Vehicle Number (column B) & Slot allotted (column C). This is where I maintain daily allotment. Column C here contains the drop-down list of Slots available for allocation.

As mentioned above, I cannot allot a slot already given to some other vehicle. So, to be extra sure I do not want my drop-down containing already allotted slots. Can I do that, if yes, how?

Answer to the above question is YES. Let’s see how. We will do it in 3 steps. Follow closely and side by side, do the same steps in your workbook too.

Step 1: Check if Value is Already Used

Since the drop-down should not contain used items, first let’s find out if any of the items is used in Allotment sheet already. For this, I use COUNTIF function.

Enter the below formula in cell B2 of Database sheet and drag it over till B11.

=IF(COUNTIF(Allotment!$C$2:$C$11,Database!A2)=1,"",ROW())

Once you copy the formula across B11, you will have the sheet like in below picture.

Formula above will check if value in A2 (parking slot number) is already allotted (in Allotment sheet C2 to C11, that’s where we have allotment column). If it is already available there then COUNTIF returns value 1 which IF function uses to return a blank value (denoted by quotes with nothing in-between). If this slot is not used yet in Allotment sheet then COUNTIF returns a value 0 which IF uses to produce the row number of the parking slot we are evaluating.

In Cell B2, since slot FF-1 is not used in Allotment sheet, row number of cell A2 which is 2 is returned by our formula. Same logic until cell B11! To cross checking our formula performance, try entering FF-1 in cell C2 of Allotment sheet and see how these values change in Database sheet. (Check the picture below, once you are done checking, delete the cell C2 entry in your sheet)

Step 2: Determine the Items to Appear in List

Now we know which items are not used yet (denoted by number in column B of Database) and which items are used (denoted by blank entries), let’s make the drop-down list with available names.

Enter the below formula in cell C2 of Database sheet and drag it over till C11. (Long formula, should be in one line)

=IF(ROW(A2)-1>COUNT($B$2:$B$11),"",INDEX($A$1:$A$11,SMALL($B$2:$B$11,ROW(A2)-1)))

Once you are done with it, your database looks like in picture below.

We will analyse this formula in parts.

Part 1: ROW(A2)-1

ROW function returns the row number where we have this formula. We are reducing this number by 1 since we are starting our formula 1 row below starting row (since we have headings in first row).

Result of this formula in our example is 1.

Part 2: COUNT($B$2:$B$11)

COUNT function will see how many cells in B2:B11 are having numbers in them. These numbers are basically the non-used items from our first step.

Result of this formula in our example is 10.

Part 3: IF function

IF function validates if the value returned by ROW function minus 1 is more or less than the number COUNT returned. If the ROW minus 1 is equal to COUNT result (that means we came to end of number of available list items), IF returns a blank entry (denoted by quotes with nothing in-between). If not then last part of the function is evaluated.

Result of IF in our example is FALSE (part 1 result is not more than part 2 result), so IF function evaluates the final part.

INDEX($A$1:$A$11,SMALL($B$2:$B$11,ROW(A2)-1)))

Part 4: ROW(A2) - 1

This part is exactly same as Part 1 above. Result is 1.

Part 5: SMALL function

SMALL function is used to return k-th smallest value in the given range. In our example, SMALL takes 1^st smallest (result of part 1) from cells B2:B11 which is 2.

Part 6: INDEX function

INDEX function returns the n-th position value in the array given. In our example, INDEX returns 2^nd (result of part 2) cell value from range A1:A11 which is cell A2 value i.e. FF-1.

So on and so forth till cell B11. Now, do check by entering FF-1 in cell C2 of Allotment sheet and see how these values change in Database sheet. This is how it looks like. (After you check, delete what you entered in cell C2)

Step 3: Apply Validation

Don’t fret. This is the last step and we are almost there. Select cells C2:C11 and apply validation. In the Allow: field of Data Validation window, select List. In the Source field, enter the below formula.

=OFFSET(Database!$C$2,,,COUNT(Database!$B$2:$B$11))

This formula creates an array from C2 of Database (where first unused item is listed) till number of non-blank cells in range B2:B11 (refer part 2 of our function analysis above). In this example, since COUNT returned 10, OFFSET function returns list starting from B2 counting 10 cells below which is B2:B11.

You can now see that cell C2, C3 and so on till C11 contains all the items in the drop-down. Now see the magic. Select FF-1 (or any item of your choice) in cell C2 (or any cell of your choice from C2:C11). Go to cell C3 (or any cell other than what you selected in previous line) and check the drop-down. Can you see the item you selected in previous line in the drop-down?

Ta da… it’s done. To get more understanding on how the list is picked up, check the Database tab every time you select an item in drop-down and see how the results of formulas are changing. You can download the workbook I used to present this example by clicking here.

I tried to explain as much as possible but I am sure something might not be well put. Let me know if any part of this post is not clear. Happy learning!!