JE McGimpsey said:
See if any of the conditions in Help ("Microsoft Excel doesn't always
display the AutoComplete list") apply.
Note: the Column *does* matter (even if you don't think it should).
I was unable to find: "Microsoft Excel doesn't always display the
AutoComplete list"
or a signifigant part of it. Nor was I able to even find "AutoComplete"
Oh well.
Below is what I found in my Excel help file about lists
Create a list
Highlight the range of data that you want make into a list.
Note You can also select the range of cells to be specified as a list by
selecting the range of cells from the Create List dialog box.
On the Data menu, point to List, and then click Create List.
If the selected data has headers, select the My list has headers check box
and click OK.
The selected range of data is highlighted by the list indicator, and the
most common list related functionality is made available on the List
toolbar.
Note If you don't see the List toolbar, on the View menu point to Toolbars,
and then click List.
After the list has been created, it will be identified by a blue border. In
addition, AutoFilter drop-downs will be automatically enabled for each
column in the list and the insert row will be added as the last row or the
list. If you choose to add a total row by clicking Toggle Total Row on the
List toolbar, a total row will be displayed under the insert row.
When you select a cell, row, or column outside of the list, the list becomes
inactive. An inactive list is surrounded by a blue border and does not
display the insert row or AutoFilter drop-downs.
Note The border will not be displayed if you clicked Hide Border of
Inactive Lists on the List menu.
About list features
When you specify a range of cells as a list in Microsoft Excel, the list
user interface integrates and makes readily available much of the standard
functionality that you might want to use on the data within that list.
AutoFilter drop-downs
One of the common things that you do with data in Excel is to filter the
data based on different field values. To make this functionality available
to you when using lists, AutoFilter drop-downs are automatically added in
the header row of a list when the list is created.
The AutoFilter drop-down contains new functionality: Sort Ascending, Sort
Descending, and other sort options are located at the top of the drop-down
list.
This functionality sorts the entire list on the field where it is chosen, in
the specified order. The remaining drop-down selections are unchanged from
the functionality in earlier versions of Excel.
Insert row
Another action that is common when you work with a list is to add a new
rows. To that end, an insert row is displayed whenever the list is active.
This interface element consists of a blank row, displayed directly below the
last row of data, with a blue asterisk (*) inside the left-most cell.
When the list is inactive, the asterisk is removed and the list border
shifts up to the bottom of the last row of data.
Total row
To display a total row, click the Toggle Total Row on the List toolbar.
This total row is displayed below the insert row when the list is active,
and shifts up to the row just below the last row of data when the list is
not active. When turned on, the word "Total" is displayed in the left-most
cell, and an appropriate Subtotal formula in the right-most cell.
The total row provides you the opportunity to display some manner of total
for all columns in your list. When you click in any a cell in the total row,
an arrow appears to the right of the cell. You can then click this drop-down
list arrow to display a number of aggregate functions. When you choose one,
a subtotal function is then inserted into that cell.
Note You cannot manually edit the cells of the total row to add different
functionality. You can select only an aggregate from the drop-down for use
in the subtotal function that is inserted into the cell by Excel.
About lists
A Microsoft Excel list provides features designed to make it easier to
manage and analyze groups of related data in an Excel worksheet. When you
designate a range as a list, you can manage and analyze the data in the list
independently of data outside the list. For example, using only the data
contained within the list, you can filter columns, add a row for totals, and
even create a PivotTable report, using only the data contained within the
list.
You can have multiple lists on your worksheet, which allows you a great deal
of flexibility for separating your data into distinct, manageable sets
according to your needs.
Note You cannot create a list in a shared workbook. You must remove the
workbook from shared use first if you want to create a list.
Excel lists make it easy to share data with other users through integration
with Microsoft Windows SharePoint Services. As long as you have the Web
address and authoring rights on a SharePoint site, you can share your list
so other people can view, edit, and update the list. If you choose to link
the list in Excel to a list on a SharePoint site, you can synchronize
changes with the SharePoint site so that other users can see updated data.
When you create a list in Excel, the features of the list and the visual
elements designed to enhance these features make it easy to identify and
modify the contents of the list.
Every column in the list has AutoFilter enabled by default in the header
row. AutoFilter allows you to filter or sort your data quickly.
The dark blue border around the list clearly distinguishes the range of
cells that makes up your list.
The row that contains an asterisk is called the insert row. Typing
information in this row will automatically add data to the list and expand
the border of the list.
A total row can be added to your list. When you click a cell within the
total row, a drop-down list of aggregate functions becomes available.
You can modify the size of your list by dragging the resize handle found on
the bottom corner of the list border.
The benefits of lists
Sort and filter lists You can sort lists in ascending or descending order
or create custom sort orders. You can also filter lists to show only the
data that meets the criteria you specify.
Ensure data integrity For lists that are not linked to SharePoint lists,
you can use the built-in data validation features in Excel. For example, you
may choose to allow only numbers or dates in a column of a list. For lists
that are linked to SharePoint lists, the list validation features of Windows
SharePoint Services are automatically applied to the list. For example, when
you publish and link the list to a server that is running Windows SharePoint
Services, or when you edit an existing SharePoint list in Excel, data type
rules are applied to each column in the list to ensure that only one type of
data is allowed in each column.
Format list objects You can format cells in a list the same way that you
format cells in a worksheet.
Compatible with lists in Windows SharePoint Services When you publish a
list to a SharePoint site, you are creating a custom SharePoint list. If you
choose to link the list when you publish it, or when you export an existing
SharePoint list, you can edit that list offline and synchronize the changes
to the SharePoint list at a later time.
Troubleshoot lists
Formulas are missing from my list.
If you add a new column that contains a formula to the list, and the list is
linked to a SharePoint site, the formula will be converted to a calculated
value.
You can retain formulas by publishing the list without linking it to
Microsoft Windows SharePoint Services.
The formula is read-only and cannot be updated in a list that is linked to a
SharePoint site.
You will see a formula in a list in Microsoft Excel only if the SharePoint
list had been exported to Excel and contains a column that has been
specified as a calculated column in a SharePoint list. This column is
read-only and cannot be modified.
I can't create a list in a shared workbook.
You cannot create lists in workbooks that are shared. Additionally, the
Share Workbook command is disabled if your workbook contains any lists. You
must first disable workbook sharing if you want to use lists in your
worksheet.
I can't create a list in a protected worksheet.
You cannot create lists in a workbook or a worksheet that is protected. You
must first unprotect the worksheet or workbook before creating lists.
I can't create a list when worksheets are grouped.
If you have multiple worksheets selected, you cannot create a list because
the worksheets have been grouped. To create a list, you must have only one
worksheet selected.
I cannot see the list border.
If you cannot see the list border when the list is not active (that is, when
a cell outside the list is selected), list borders have been hidden for
inactive lists. On the Data menu, point to List, and then click Hide Border
of Inactive Lists.
When I type in a row directly under a list, the list does not automatically
expand.
If you have the total row displayed in the list, the list will not expand
automatically when you type in the row below the list.
I made changes to a list linked to a SharePoint list and I can't update my
changes because the list has been modified on the SharePoint site.
If the SharePoint list has setting changes to a column that been modified in
Microsoft Excel, you will lose your changes if you choose to Refresh your
list in the dialog box that is displayed when you try to synchronize. If you
want to keep the changes you made in Excel, you must click Cancel in this
dialog box.
I can't publish a list because I have too many columns in the list.
There are limitations to the number of columns for each data type when
publishing a list.Data type Number of columns
Short text 64
Multi-line text 32
Number 32
Currency 32
Date/Time 16
Hyperlink 64
When I copy and paste data in a list, and then type new data in the cell
where I've pasted, I get a data validation error, even though my data
conforms to the type restrictions of the column.
When you copy a cell by dragging or by clicking Cut or Copy , and Paste ,
Microsoft Excel copies the entire cell, including formulas and their
resulting values, comments, and cell formats.
For example, if you copy a number from a cell in a column that has been
specified as a column of text and paste it into a cell in a column that
requires a number, you will get a data validation error. If you then try to
type a number into that cell to resolve the error, you will again get a data
validation error. This validation error occurs because the format for the
cell changed to text when you copied data to the cell from another cell that
has text format. In text format cells, numbers are treated as text.
To resolve this error, change the format of the cell back to a format that
is suitable for the data type. For example, to correct the problem described
above, do the following:
Select the cell you want to modify and click Cells on the Format menu.
On the Number tab of the Format Cells dialog box, select the General format.
Click OK to apply the format.
I get an unexpected error when I try to publish a list.
When you publish a list, unexpected errors may occur for a number of
reasons.
You may not be able to access the Microsoft Windows SharePoint Web site. The
URL specified for the SharePoint site may be incorrect or an administrator
may have configured the SharePoint site to deny access.
The name of the list may be too long. Give the list a shorter name before
trying to publish it again.
You may have cancelled the publish operation while data was being
transferred to the SharePoint site.
There may be a version conflict between Microsoft Excel and the SharePoint
site.
There may have been a schema conflict between the list in Excel and the list
on the SharePoint site.
