Updating a Validation List

[Guest]

Hello,

Does anyone know how to update a validation list just by typing a new value
into the cell with the list. Inotherwords, if I have a validation list in
cell (a) that has apples, oranges and pears, if I type in bananas, I want
that entry to fill my validation list.

Thanx for your help

 

[Guest]

Try this code in the module associated with the sheetwith the data and
validation on.

Relies on all the validated cells being in a named range 'validation' and
the validation is from the named range 'fruit'. This is trying to avoid
calling all of the code every time anything changes on the sheet.

I can mail you the workbook if you drop me a mail ... remove NO and SPAM from

(e-mail address removed).

Regards,

Chris.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ranCell As Range
Dim ranTarget As Range

For Each ranTarget In Target.Cells

If Not Intersect(ranTarget, Range("validation")) Is Nothing Then

Set ranCell = Range("fruit").Find(What:=ranTarget.Value)

If ranCell Is Nothing Then

Range("fruit").Cells(Range("fruit").Cells.End(xlDown).Row +
1, 1) = ranTarget.Value

End If

End If

Next ranTarget

End Sub

 

[Guest]

or possibly: <If you don't want to use worksheet events and code>

You could use a named dynamic range to reference the list in the DV dialog.
This will allow you to add/remove items randomly and the list automatically
updates. This doesn't require any code or events procedures.

**********
Here's how:
**********
1. Determine the number of possible entries that might be required for list
items.
2. Define a local named range where the list will be located, having the
determined number of cells. Call it "Fruits", for example. (It won't matter
how many extra cells it contains)
3. Define a global name like "FruitsList", for example, that contains the
following formula in its RefersTo box:

=OFFSET(Fruits,0,0,COUNTA(Fruits),1)

This creates a range name "FruitsList" that consists of the number of
entries in Range("Fruits"). It will change size as items are added/removed,
which is why it's called 'dynamic'.

The above formula assumes there's no header included in the reference for
"Fruits". If you want to include it in the range definition, then the formula
should be:

=OFFSET(Fruits,1,0,COUNTA(Fruits)-1,1)

This will capture only the listed items below the header.

4. In the DV dialog, with 'List' selected, enter =FruitsList in the Ref
Edit box.

That's it!

Regards,
GS

 

[Debra Dalgleish]

There are sample files here:

http://www.contextures.com/excelfiles.html

Under 'Data Validation'

To do this with formulas, look for 'Flexible Item List'

Or to do it programmatically, look for 'Update Validation List'