• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Autocomplete based on multiple lookup columns on another tab

Hi Experts

I have been struggling with this problem trying to work it out and can't seem to make it work through the examples I have found.

I have an Excel document which has 2 tabs. The first is a user input template and the second has multiple columns of lookup data. As the user inputs data in the template, I need the columns, 'School', 'Subject' and 'Year Levels' to autocomplete based on the entries in its corresponding column on the Data-Lookup tab. Data input is to be restricted to only the entries in the corresponding list. Is there anyway to do this with code? I have attached an example for reference.

Thanks in advance
darls15 EE-Example-01052011.xls
0
darls15
Asked:
darls15
  • 4
  • 2
1 Solution
 
honestman31Commented:
Can you provide more data in your example plz
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

do you mean autocomplete as in "fill in the actual values automatically"? If so, there is no data to do that. You enter the name, you enter the school, but there is no way of autocompleting the subject or year.

Or do you want to have a drop-down list for school, one for Subject, one for Year level, etc? In that case, you need to use data validation with the List option in the User Input sheet. On the Data Lookup sheet, you can define range names for the list of valid ranges in each column, and then enter that range name in the data validation for the cell on the User Input sheet.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
see attached file for data validation in place for the first 13 or so rows. If that is not what you need, please provide more information about what you want to achieve.

cheers, teylyn
Copy-of-EE-Example-01052011.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
darls15Author Commented:
Hi,
 
I'm sorry for the lack of information, I will try to explain further.

By autocomplete I mean that when a user starts to type the first letter of an entry from one of the "lookup" columns, it will automatically populate the whole word. For example, in Subject, if the user types M, then Mathematics would appear/complete. I don't want users to be able to add new items, only those that are in the corresponding column on the lookup tab. So, if the user types a word not in this list it will prompt them to try again with a message saying something like, "selection not available".

Teylyn's solution achieves a similar result through the use of dropdown lists, however, I am still needing the functionality to be able to navigate to the word, e.g. M - Mathematics, as the actual list is considerably longer than this example.

I would prefer a user template without dropdown lists, however, either method would be a good result.

I hope I've explained this enough, please let me know if you need any further information.

Thanks so much
darls15
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Excel does not do auto-complete like this very well. If you have a list of items, though, and start typing an item that has been used in one of the rows above, Excel will do an auto-complete as soon as enough letters have been entered to make the value distinguishable.

This can be combined with a data validation list, where the drop-down has been turned off.

In the attached file, the valid options have been entered in hidden rows below row 1. The data starts in row 14 and this row, as well as the next few rows below, have data validation applied as described previously, but the option "in-cell dropdown" has been unticked.

Start typing a valid school or subject and you'll see the values auto-complete. For the Year Level, this is not quite as apparent, since it's mostly a single digit. This auto-complete happens because of the values in the hidden cells, and the data validation ensures that only the specified values may be entered.

cheers, teylyn
Copy-of-Copy-of-EE-Example-01052.xls
0
 
darls15Author Commented:
Thanks teylyn for all your help, your solution works brilliantly. I appreciate your prompt replies.
darls15
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Glad it works for you. Thanks for the grade!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now