Part A: Set up the NAME- Set up a DropDown xsheet
- Add some values into the DropDown xsheet to form a list - say in column A and using the first row for headings
- Start defining a NAME (Insert->Name->Define)
- Enter a name for the NAME (e.g. ListOfXXXX)
- Enter the magic formula =OFFSET(DropDown!$A$2,0,0,COUNTA(DropDown!$A:$A)-1,1)
- This defines the NAME as everything from row 2 to the first empty cell in column A.
- To add extra values, just add to the first blank cell at the bottom of the list
- 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- 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)
- Don't forget the "=" sign in the source
- Press OK - job done.
- 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
|
