microsoft excel training - validation lists

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel training - Validation lists

microsoft excel training - Validation lists

resolvedResolved · Low Priority · Version Standard

Nick has attended:
Access Introduction course

Validation lists


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??

RE: Validation lists

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


 

Excel tip:

Display pictures on Chart Data Point

Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.

Step 2: Right click on the bar and select Format Data Point.

Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.

View all Excel hints and tips


Server loaded in 0.09 secs.