Excel tip: Automate data validation lists

Kashflow logo

David H Ringstrom continues his series of productivity suggestions with a tutorial explaining how to make Excel’s data validation facility work even more smoothly.

If there’s one useful principle you should bear in mind when designing a spreadsheet that will be used by other people, it would be to apply Excel’s Data Validation feature wherever possible.

Rather than letting users loose to enter any value in any cell they like Data validation restricts them to a predefined set of values. This tutorial shows how to create in-cell drop-down lists, and configure them so you won’t need to adjust it if you add additional items in the future. This technique also addresses an Excel 2007 flaw that prevents you from placing validation lists in other worksheets.

To demonstrate how Data Validation works it’s traditional to use a selection of fruit as an example. On the second worksheet of a blank workbook, create a list of fruit...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About David H Ringstrom

Replies

Please login or register to join the discussion.

avatar
13th Dec 2013 16:22

new trick for an old dog!

Thanks, I'm a big user of dynamic name ranges for all types of analysis from validation to use in arrays, normally using the Offset() function and Counta() to position and size used rows or (columns) into a range.  David's suggestion benefits from two points: firstly the table items are defined as contiguous, Offset/Counta could give unexpected results if there is data placed someway below the list in the worksheet as the counta() function normally link to the entire worksheet column; and this is simpler to setup than via a NameRange Function and you can see exactly the extent of the range quickly. I like it.

Thanks (0)
avatar
By waltere
18th Dec 2013 12:51

I agree!

A simple topic, clearly explained - thanks!

Thanks (0)
avatar
By chatman
09th Jan 2014 16:32

Good Tip. Thanks.

Thanks (0)
avatar
11th Jan 2014 16:59

GREAT TIP, CLEARLY EXPLAINED VIA A SIMPLE EXAMPLE. THANK YOU.

Thanks (0)

Related content