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

Change in list details for data validation

ResolvedVersion 2003

Thu 4 Feb 2010, 16:18 Delegate 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?

Fri 5 Feb 2010, 15:42 Trainer 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

Training courses

98.6 % recommend us

More live stats...

Training information:

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips

Change in list details for data validation

Change in list details for data validation

RE: Change in list details for data validation

Excel articles

Excel tip:

Sorting List Subtotals