xxxxxxxxxxxxxxxxxxxxxxxxxxx
EXCEL WIKI ExcelWiki.com
An Excel Spreadsheet Excelclopedia

Drop Down Lists-Remove Previously Used Items

Previously used items can be removed from dropdown lists. This can be useful in scheduling employees so that they are not assigned two tasks at the same time. The list length decreases with each assigned task. In the example below Grant and Galloway have already been assigned tasks and are thus removed from the dropdown list.

 

An example Excel file is attached and is explained below. Attach:NoDupsDropDown.xls This file does not use macros.

 

 

Step by Step

  • Assign a name to the Data Validation range (the user input range). In this example the name OutPutRange is assigned to the (yellow) Data Validation range.
  • Create and name a three column source range. In this example the (blue) source range is assigned the name SourceRange. The source range in this example is on a different sheet than the user input area.
  • The first SourceRange column contains the master list of items (employees).
  • The formula in the second Sourcerange column identifies the items (employees) that have not been assigned by the user.
  • The formula in the third SourceRange column sorts these items and removes blank lines. The valid entries in this thrid column are the available remaining inputs for the dropdown list.
  • Input the data validation list into the first column.
  • Verify the formula at the top of the second column and copy it down the 2nd column. Note that the B3 in this fomula refers to the top cell in the first column. =IF(ISNA(MATCH(B3,OutputRange,0)),ROW(),"")
  • Verify the formula at thh top of the third column and copy it down the 3rd column. Note that the B3 in this formula refers to the top row in the first column and the C$3 and C3 to the top row in the second column. =INDIRECT(LEFT(CELL("address",B3),3)&SMALL(OFFSET(SourceRange,0,1,ROWS(SourceRange),1),ROWS(C$3:C3)))

 

  • In the first row of the (yellow) OutputRange click on Data-validation and enter this formula into the Source box. =OFFSET(SourceRange,0,2,COUNTIF(SourceRange,"<>#num!"),1)
  • Copy this Data Validation down to all rows in the Data Validation range.

 



© 2005-2009 Excelwiki.com