Autocomplete Not Working As Expected When Data Validation List Uses OFFSET Formula

I have found that if I...

1) Create a named range using an OFFSET formula, and
2) Use this name as a basis for a large number of data-validated cells within a column,

If I choose to type in an empty data-validated cell, it will suggest autocomplete options only if it is immediately above or below a previously populated cell.

At first, I thought it would suggest only from the items immediately next to it (contiguous), but this is not the case either. In fact, the autocomplete options available revert to all relevant items in the named range - as expected.

I have several other columns of data-validated cells, some based on direct entry lists and others using named ranges without OFFSET formulas and they all work fine. I created the OFFSET named ranges to allow a user to add to these lists.

Thanks for help or confirmation that this is the way it works.

I am using Excel for Mac 2011. I updated my profile, but this post may not reflect this change and still say I use Excel 2010.

This is the way it works. Data Validation does not offer autocomplete. The autocomplete you are getting is available in any cell based on data that already exists in the column above it.

There are workarounds but it's hard to recommend anything specific without seeing your file.

Originally Posted by juanamaral

I am using Excel for Mac 2011. I updated my profile, but this post may not reflect this change and still say I use Excel 2010.

Your current profile is shown on all posts, even old ones. So you did not successfully change the Office version in your profile.

This works with a dynamic named range:

=List!$A$1:INDEX(List!$A$1:$A$1000,COUNTA(List!$A$1:$A$1000))

Enable macros on opening.