List data validation suggested completions list is not fuctioning correctly in column A:A

Question

Hi,&nbsp;I've noticed Data Validation is not listing suggested completions, in a dropdown list shortened from the original list to include only valid completions, for column A:A only.&nbsp; (It used to).&nbsp;Inserting a blank column A:A resolves the issueUnchecking {View, Headings} resolves the issueOnly some users are affected&nbsp;The user affected has the app based version of Excel through Office365, but others also have exactly that setup, and don't have difficulties.&nbsp;The list Data Validation references a named table on another sheet, that way users can extend the valid inputs easily, should they need to.&nbsp;Why is only the first column affected? And why does inserting a blank column resolve it?&nbsp;I should add, the cut down allowable completions list is visible for about 1/100th of a second, but only once. Then won't appear again until you click away and click back.&nbsp;Also, the dropdown arrow still provides access to the scrollable list in full.&nbsp;Columns other than A:A on the same sheet have the same {named table,&nbsp;list Data Validation} arrangement and work perfectly.&nbsp;Search engines suggest I'm not alone. (But then I got accused of hijacking someone else's thread, so I hightailed it here instead!)&nbsp;Thanks,&nbsp;Michael.

jkpieterse · Answer

shafarevich&nbsp;Can you share a stripped-down version of a file that exhibits the behavior by any chance? (please remove sensitive information first!)

shafarevich · Answer

JKPieterse&nbsp;&nbsp;The slight issue is that this file works fine for me, browser based and app.&nbsp;Additionally, the user believes he has resolved the issue by updating the version of Teams he is running.&nbsp;He highlights that he no longer has the option to upgrade.&nbsp;He thought the error stemmed from having 2 versions of Teams conflicting? I'm not sure this is correct.&nbsp;Anyway, this file captures the basic design that used to failed only on column A on his machine.&nbsp;I had to use INDIRECT to get the table references to work. (I mean, exactly as I had to originally, this is the stripped down structure verbatim).

shafarevich · Answer

@Jan Karel Pieterse&nbsp;&nbsp;The unhelpful Excel behaviour is visible to everyone using the previously attached file, Data validation column A.xlsm, if you open it in browser Excel, and make sure the Zoom is not 100%.&nbsp;Then, the filtered list, of acceptable continuations, given what you have already typed, is invisible in column A:A only.&nbsp;So, this isn't a fix, but you should be able to replicate the problem yourself, which might be helpful?

jkpieterse · Answer

shafarevich&nbsp;It works fine for me too, both online and in the app.

shafarevich · Answer

JKPieterse&nbsp;Right, fair point. Last bit of information with any luck, the Excel spreadsheet is shared via Teams. You need to access the Excel spreadsheet as a Teams shared file, in the browser version of Excel, and with the Zoom not at 100%. Hopefully, with that one outstanding detail, you will also be able to see, there's a snag displaying the filtered list of potential acceptable completions. Sorry, I didn't understand that that information was relevant earlier.&nbsp;And this fault is only in the browser version of Excel.&nbsp;Also, I can confirm that when accessed, as I expect you will have done, directly as a downloaded spreadsheet, there is no problem. But shared via Teams, the problem is there.&nbsp;Unnecessary detail perhaps, but the file belongs to a group, of which I'm a member. So it isn't exactly my file, it's the group's.

Forum Discussion

List data validation suggested completions list is not fuctioning correctly in column A:A

8 Replies

Resources