I have recently been asked if it’s possible to have a data validation list on another sheet. Well, of course it is. This is particularly useful if there is a danger of your list being overwritten or erased, so sometimes its better to have it on a completely separate sheet.

Let’s have a look at it step by step.

1. Enter your data list into Excel.
2. Highlight your data list
3. Go to cell reference area and type a name for your range of cells

1. Highlight the cells you want to apply the validation to OR Shortcut ALT, D,L
2. Select List in validation criteria- allow
3. In source type = and your named range the = (equals) sign is REALLY important OR hit F3 and select your named range
4. Hit Ok
5. Test it!!!!
6. If you really want to turn up the volume on this and have your off sheet data validation range become dynamic and update and expand as rows are added, then all you need to do is make the named range dynamic.

All you need to do is

1. Highlight your data range
2. Hit CTRL+T ( Excel 2007) to CTRL+L ( earlier versions of Excel) to have data become a table
3. Confirm if your data has headings
4. Hit Ok.

Other Data Validation Posts

1. Setting Up Data Validation Lists

2. Data Validation Using A Dynamic List

3. Data Validation- check for repeating values