|You can change any of these cells to whatever you want, these are just examples to show you the concepts. You can put the "extra" cells anywhere you want, even on another sheet.|
1 - A1 will be the cell that will contain either N/A or the "anything else " you mentioned.
2 - B1:B4 will be the range that contains your "long" list of choices for the Drop Down.
- Select B1:B4 and name the range myRng
3 - C1 will contain the text N/A
- Select C1 and name the cell NA
4 - D1 will contain this formula:
5 - E1 will contain your Drop Down
- Set up the Data Validation using List and enter this in the Source field:
How this will work is as follows:
When A1 contains N/A, D1 will display NA. The INDIRECT formula for the Data Validation List will use the Named Range NA to populate the list. Since the range named NA consists only of C1, only N/A will appear in the Drop Down.
When anything else is in A1, D1 will display myRng and the Drop Down will be populated with the data from the range named myRng (B1:B4).
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.