The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Change in list details for data validation

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Change in list details for data validation

Resolved · Low Priority · Version 2003

ReplyThu 4 Feb 2010, 16:18Delegate Geoff said...

Change in list details for data validation

When you create a list for data validation from names range details on a sheet, how do you update the list to include the newly increased or reduced range?

For upcoming training course dates see: Pricing & availability

ReplyFri 5 Feb 2010, 15:42Trainer Stephen said...

RE: Change in list details for data validation

Hi Geoff

Thanks for your question

The following steps will create a dynamic range which will automatically resize according to the items in your list

Create a named range using Insert-Name-Define.

Give your range a name

In the refers to box type the following equation

=OFFSET($D$1,0,0,COUNTA($D:$D),1)

This uses the CountA function nested within the Offset function.The offset function specifies a start point of cell D1 and a finish point based on the number of populated cells in column D.

Hope this helps,

Regards

Stephen

Excel articles

· How Teachers Are Using Microsoft Excel To Enhance Learning in the Classroom
· Some Mouse Click Tips In Excel
· Do You Need To Run Excel 2007 In Compatibility Mode?
· How To Convert Text To Columns In Excel
· VBA Excel 2007

Please browse our web site to find out more about
excel consulting and other Microsoft training courses.

Excel tip:

Add Text to Displayed Numbers in Excel 2010

To add text to a number in a cell, you need to go to the Home tab on the Ribbon, and click on the Cells group. Select Format Cells from the drop down menu then Custom from the Category list. In the Type box select General. After the word General, enter a space, then opening quotation marks, then the word you want to type and then closing quotation marks. Click on OK and you have your text!

View all Excel hints and tips

How do you get rid of clipboards for all time?

» Forum post: Excel

Rate this page:

Currently 2.15/5
1
2
3
4
5

2.2/5 (213 votes cast)

Add to:

Follow @beststl on Twitter

© 2012 Best STL. All Rights Reserved. A Microsoft software training company. info
All prices offered for business users and exclude VAT. E&OE · Terms and Conditions