How to get 'auto-complete' to work in an excel list being used to validate data entry?
November 8, 2010 8:09 AM Subscribe
Excel - How to get 'auto-complete' to work in an excel list being used to validate data entry?
I have a spreadsheet with 5000 records. I have to assign each to one of 42 categories. I have created a list of the 42 categories and placed it in each of the 5000 rows. I hate using my mouse and would like to categorize each record without using my mouse. I have learned that ALT + Down Arrow will drop down the list.
What do I now need to do to allow me to start typing the category name and as soon as it is highlighted, hit return to select it? Y'know auto-complete.
Excel 2003 by the way.
I have a spreadsheet with 5000 records. I have to assign each to one of 42 categories. I have created a list of the 42 categories and placed it in each of the 5000 rows. I hate using my mouse and would like to categorize each record without using my mouse. I have learned that ALT + Down Arrow will drop down the list.
What do I now need to do to allow me to start typing the category name and as soon as it is highlighted, hit return to select it? Y'know auto-complete.
Excel 2003 by the way.
Response by poster: From the main menu I took the following steps:
Data --> Validation -->
In the Allow List dialog selected the following values: Source (I Selected the 42 categories i.e. highlighted the list of the categories elsewhere in the sheet). Ignore Blank and In-cell dropdown are checked.
Hope this helps. I will look at the linked you posted now. Thanks.
posted by therubettes at 8:31 AM on November 8, 2010
Data --> Validation -->
In the Allow List dialog selected the following values: Source (I Selected the 42 categories i.e. highlighted the list of the categories elsewhere in the sheet). Ignore Blank and In-cell dropdown are checked.
Hope this helps. I will look at the linked you posted now. Thanks.
posted by therubettes at 8:31 AM on November 8, 2010
Best answer: Yeah, so it sounds like your autocomplete issue is one of two things. Either you simply haven't selected all 42 of your categories yet or there are blank cells (spacing lines etc) in your category column. Each blank cell will effectively reset the autocomplete.
So, to make the autocomplete work regardless, here's a workaround:
1) Go through your entire spreadsheet and ensure you remove any blank cells or lines.
2) Create 42 dummy entries right at the top of your spreadsheet, one for each category, clearly labelled 'DUMMY'. Hide those rows. Start entering data. All 42 categories should now autocomplete. Once you're done, either leave the dummy rows in place and hidden (if other people are entering data, or your spreadsheet is going to have additional content added in future), or delete them.
posted by Happy Dave at 8:38 AM on November 8, 2010
So, to make the autocomplete work regardless, here's a workaround:
1) Go through your entire spreadsheet and ensure you remove any blank cells or lines.
2) Create 42 dummy entries right at the top of your spreadsheet, one for each category, clearly labelled 'DUMMY'. Hide those rows. Start entering data. All 42 categories should now autocomplete. Once you're done, either leave the dummy rows in place and hidden (if other people are entering data, or your spreadsheet is going to have additional content added in future), or delete them.
posted by Happy Dave at 8:38 AM on November 8, 2010
Response by poster: Either you simply haven't selected all 42 of your categories yet or there are blank cells (spacing lines etc) in your category column.
Pretty sure this is not the case as all 42 are present and correct when I drop down the contents of the cell. There are no blanks.
Create 42 dummy entries right at the top of your spreadsheet, one for each category, clearly labelled 'DUMMY'. Hide those rows. Start entering data.
Great solution. Achieves exactly what I needed. Thanks.
posted by therubettes at 8:53 AM on November 8, 2010
Pretty sure this is not the case as all 42 are present and correct when I drop down the contents of the cell. There are no blanks.
Create 42 dummy entries right at the top of your spreadsheet, one for each category, clearly labelled 'DUMMY'. Hide those rows. Start entering data.
Great solution. Achieves exactly what I needed. Thanks.
posted by therubettes at 8:53 AM on November 8, 2010
Is there any concise rule that expresses which category a particular row belongs to, or is this something that a human absolutely has to decide on a per row basis?
posted by atrazine at 9:26 AM on November 8, 2010
posted by atrazine at 9:26 AM on November 8, 2010
Pretty sure this is not the case as all 42 are present and correct when I drop down the contents of the cell. There are no blanks.
That's not what I meant - the 42 will show up in the dropdown fine, of course. However, autocomplete is not governed by content of your dropdown, it's governed by what has appeared in cells in the same column above - it's basically a record of the entries above it. If you 'break' that column with a blank cell, it will reset the autocomplete. So, if your 5000 rows of data have any blank rows, your autocomplete will start from scratch each time.
Great solution. Achieves exactly what I needed. Thanks.
Cool, glad to help.
posted by Happy Dave at 9:30 AM on November 8, 2010
That's not what I meant - the 42 will show up in the dropdown fine, of course. However, autocomplete is not governed by content of your dropdown, it's governed by what has appeared in cells in the same column above - it's basically a record of the entries above it. If you 'break' that column with a blank cell, it will reset the autocomplete. So, if your 5000 rows of data have any blank rows, your autocomplete will start from scratch each time.
Great solution. Achieves exactly what I needed. Thanks.
Cool, glad to help.
posted by Happy Dave at 9:30 AM on November 8, 2010
Response by poster: A human absolutely has to decide on a per row basis?
Sadly yes, these are 5000 strays that did not directly fit the original selection criteria.
posted by therubettes at 9:45 AM on November 8, 2010
Sadly yes, these are 5000 strays that did not directly fit the original selection criteria.
posted by therubettes at 9:45 AM on November 8, 2010
« Older Where can I purchase taco bowls? | Water Damaged Camera - Any Chance at Reasonable... Newer »
This thread is closed to new comments.
Not sure what you mean by this. Do you mean you're using Data Validation rules to refer those cells to a list of allowed values?
Generally, autocomplete will only work where you have previously entered the same value, and then only when the cell in question is physically in the same column with no blank cells between as the earlier instance.
Here's a quick tutorial on data validation lists.
Here's more on the blank cell rule that stops autocompletes.
posted by Happy Dave at 8:25 AM on November 8, 2010