Wednesday 5 December 2007

Excel: Dynamic drop downs using Names

Part A: Set up the NAME
  1. Set up a DropDown xsheet
  2. Add some values into the DropDown xsheet to form a list - say in column A and using the first row for headings
  3. Start defining a NAME (Insert->Name->Define)
  4. Enter a name for the NAME (e.g. ListOfXXXX)
  5. Enter the magic formula =OFFSET(DropDown!$A$2,0,0,COUNTA(DropDown!$A:$A)-1,1)
  6. This defines the NAME as everything from row 2 to the first empty cell in column A.
  7. To add extra values, just add to the first blank cell at the bottom of the list
  8. Notice the hardcoded sheetname and the references to the column, also that the list starts at row 2; any changes to these will mean the name needs to be redefined.

Part B: Use the NAME to create a drop down via cell validation
  1. Go to a data sheet and highlight the whole target column - then define a data validation (Data->Validation) with Allow of "List", Source of "=ListOfXXXX" (ie. the defined NAME)
  2. Don't forget the "=" sign in the source
  3. Press OK - job done.
  4. Tidy up by Edit->Clear->All on the top heading rows
Part C : Extras
  • You can define a static name using hardcoded references in the NAME instead of the black magic formula, but adding additional values will mean re-editing the NAME
  • A NAME can be used in multiple validations
  • The DropDown sheet can be hidden (Format->Sheet->Hide) and protected
  • To allow blanks remove the -1 (minus one) from the end of the black magic formula to allow the range to include the first blank cell