darls15
asked on
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
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
Can you provide more data in your example plz
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
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
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
cheers, teylyn
Copy-of-EE-Example-01052011.xls
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks teylyn for all your help, your solution works brilliantly. I appreciate your prompt replies.
darls15
darls15
Glad it works for you. Thanks for the grade!