Change in list details for data validation

The UK's most regular instructor-led training courses.
Training information: excel consulting · Excel courses · Microsoft Excel Training UK
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version 2003

1 post

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

Geoff has attended:
Excel Advanced course

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?

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Wed 17 Mar 2010	Bloomsbury	1		£235	£240
2007	Fri 19 Mar 2010	Bloomsbury	0	FULL
2003	Wed 24 Mar 2010	Limehouse	8		£207	£212
2007	Fri 26 Mar 2010	Rochester (Hoo)	0	FULL
2007	Mon 29 Mar 2010	Bloomsbury	3		£235	£240
2003	Wed 31 Mar 2010	Southwark	8		£214	£219
Full Schedule: See all 71 Excel Advanced course dates. Bookings currently available until 22nd December 2010.

748 posts

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

· Microsoft Excel Features and Functions for Accountants
· Using Dates Within Excel
· Why do we even need XML anyway?
· How Excel Training Can Improve Your Company's Sales Process
· So Why Are There So Many File Types In Excel 2007?

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

Excel tip:

Converting an American date format to European using Formula

Excel depending on your local setting will only pick up date values of the dd mmm yyyy oders as date type. If you import data from various sources including America their date order is different with data value in mmm dd yyyy, excel can only treat it as text indicated by left aligning it. To overcome this you have to do the the following.

1. Extract the date components mmm dd yyyy, by using the the Text functions LEFT, MID or RIGHT

2. Reorder dd mmm component and concatenate using "&" in the right order this will create a text string with the date in the right order it then needss to be converted to a value so excel can recognise it.

3. To convert to value encase in TEXT function.

4. Format to desired date format.

View all Excel hints and tips