Validation question

[Harvey Waxman]

In a column that validates from a list, typing an entry displays autofill data
rather than the list data. I have to click the selection arrows to show the
list or else turn of the autofill feature.

Is there a way to purge the stored autofill data from this column?

Thanks

(ExcelOSX)

 

[JE McGimpsey]

In a column that validates from a list, typing an entry displays autofill
data
rather than the list data. I have to click the selection arrows to show the
list or else turn of the autofill feature.

Is there a way to purge the stored autofill data from this column?

Autocomplete (not autofill) data is generated on the fly, so there's
nothing to purge. You could turn it off altogether in
Preferences/Edit/Enable autocomplete...

If you don't mind using an event macro, you could easily turn it off.
Put this event macro in your worksheet code module (CTRL-click the
workbook Title bar and choose View Code):

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)
On Error Resume Next
Application.EnableAutoComplete = _
Intersect(Selection, Sheet1.Columns(5)) Is Nothing
On Error GoTo 0
End Sub

Change your sheet's CodeName (here, "Sheet1") and column (here, 5) to
suit.

 

[Harvey Waxman]

Autocomplete (not autofill) data is generated on the fly, so there's
nothing to purge. You could turn it off altogether in
Preferences/Edit/Enable autocomplete...

Sorry for the wrong terminology. I wonder how it 'knows' how to autocomplete
unless it has something with which to compare it from prior entries. Isn't
that stored somewhere?

For example, I can start typing in a column and choices pop up which are not in
the source list. This is what I'd like to prevent while retaining that feature
for other columns.

Maybe copying to a new worksheet, leaving the unwanted stuff behind and
recreating the validation list.

Thanks

 

[JE McGimpsey]

Sorry for the wrong terminology. I wonder how it 'knows' how to autocomplete
unless it has something with which to compare it from prior entries. Isn't
that stored somewhere?

The storage for autocomplete is the cells in the column above your entry
cell.


See Help: "Quickly fill in repeated entries in a column" for the
explanation of how autocomplete works.

 

[Harvey Waxman]

The storage for autocomplete is the cells in the column above your entry
cell.

That helps. Thanks