Is it possible to create a 'validation list' in a different sheet to the sheet being worked in?

I am creating a large model and would like to keep all of the validation lists in a seperate worksheet however excel only appears to allow to let you have the valifdation list int he sheet you're working in - is there any way around this??

Hi Nick

Yes you can do this by using NAMED RANGES. We cover this in our advanced excel course.

Using the standard Data Validation process, you are only able to specify lists within the current spreadsheet. Using named ranges, you can create a range on any sheet in the workbook, and simply link to it.

Here are the steps:

To create NAMED RANGE:

1. Select the range you want to use
2. In the NAME BOX (Top left corner, far left of formula toolbar), type the name for your range (lets call it ABC)
3. Hit ENTER (very important - this creates the name).


To use NAMED RANGE in DATA VALIDATION

1. Select the range you want to validate
2. MENUBAR > DATA > VALIDATION
3. Allow LIST
4. =ABC

That will link the named range to the list.

Let me know how it goes,

Richard