Auto-complete limited to 121 cells?

I'm having problems with auto-complete in Mac for Excel 2010.

I have a long list of data, but it looks as though auto-complete is only looking at the last 121 entries above the cell that I am inputing data in.

In the attached, if you go to the last cell in the column (the yellow cell) and type 'c', you'll notice that the word that is being offered in auto-complete is not the first 'c' in the list. Rather it is the first 'c' about 121 entries above the input cell.

So it looks to me as though there may be a limitation in auto-complete, in that it will only look at the 121 preceding cells.

Does anyone know how I can extend the list to ~300 cells?

Thanks, twobob

autocomplete test.xlsx

I've been playing around a bit more with auto-complete, and it looks as though auto-complete has a number of limitations:

- if inputing text into a cell at the bottom of the list, auto-complete only looks at the 120 rows above it
- if inputing text into a cell within a list, auto-complete only looks at the 60 rows above it, and the 60 rows below it

I am trying to build a worksheet for my team to input a category code in a cell and a list appears as part of auto-complete, however my list is longer than 120 rows. Does anyone know a workaround that will allow auto-complete to work with a longer list?

Attached is an auto-complete example.

autocomplete test 3.xlsx

At the bottom is an input cell. If I type '1', I would expect all rows that start with a '1' to appear as part of auto-complete. Due to the excel limitations, nothing appears when I type '1', and when I type '2', only the 120 rows immediately above the input cell appear in auto-complete.

Grateful for help on how to do a work-around!

Moderators - perhaps I've put this into the wrong forum? If so, please feel free to move to the correct one. Thanks!