Create Autocomplete Drop Down List on Spreadsheet.
Hi Guys,
I would like to create an autocomplete drop down list on certain columns in my invoice.
The list of items can be found in the worksheet Zones + Tolls
The dropdown lists need to be in Column E, column F and column M for the list of Suburb / Zones.
The dropdown lists need have an autocomplete feature, so that if I type alp, the list shows me the item that begin with alp, etc.
Also the lists should always show the content, when selected in capitals, regardless of whether I have the caps lock on or off.
I have attached my blank template, so that whatever is needed can be added to the template. I have tried data validation, combo box and can't understand it and even had the entire list below the cells and it doesn't always work.
Also, as this is an invoice template I use, I would also need the ability to when I copy entire rows to insert new rows, that the dropdown list automatically is added into the new inserted rows.
01-Invoice-Template-blank.xls
I would like to create an autocomplete drop down list on certain columns in my invoice.
The list of items can be found in the worksheet Zones + Tolls
The dropdown lists need to be in Column E, column F and column M for the list of Suburb / Zones.
The dropdown lists need have an autocomplete feature, so that if I type alp, the list shows me the item that begin with alp, etc.
Also the lists should always show the content, when selected in capitals, regardless of whether I have the caps lock on or off.
I have attached my blank template, so that whatever is needed can be added to the template. I have tried data validation, combo box and can't understand it and even had the entire list below the cells and it doesn't always work.
Also, as this is an invoice template I use, I would also need the ability to when I copy entire rows to insert new rows, that the dropdown list automatically is added into the new inserted rows.
01-Invoice-Template-blank.xls
ASKER
Hi MartinLiss,
Thank you for the template. It does do what I want, however here are my few dilemmas.
1) It is an xlsm file and I would prefer if it remained as an xls file, as some of my staff don't have the newest Office Applications.
2) While the autocomplete works, I find having to double click on the field, is annoying. When you have to do nearly 200 + jobs per day, this can be very time consuming and frustrating. I was hoping the autocomplete simply works from the actual cell input. So as they tab to that the cell in the column, they simply enter the letters and it autocompletes/fills with the correct Suburb / Town.
3) Column F appears to not have the code.
Is there any way this can be resolved?
Thank you for the template. It does do what I want, however here are my few dilemmas.
1) It is an xlsm file and I would prefer if it remained as an xls file, as some of my staff don't have the newest Office Applications.
2) While the autocomplete works, I find having to double click on the field, is annoying. When you have to do nearly 200 + jobs per day, this can be very time consuming and frustrating. I was hoping the autocomplete simply works from the actual cell input. So as they tab to that the cell in the column, they simply enter the letters and it autocompletes/fills with the correct Suburb / Town.
3) Column F appears to not have the code.
Is there any way this can be resolved?
1) Corrected
2) Corrected
3) Yes that's expected. I only did the code for column E. I can easily add the code for column F and column M once you say that the code in E is what you want.
01-Invoice-Template-blank.xls
2) Corrected
3) Yes that's expected. I only did the code for column E. I can easily add the code for column F and column M once you say that the code in E is what you want.
01-Invoice-Template-blank.xls
ASKER
Hi MartinLiss,
That is perfect. Works a treat. Thank you.
Are you also able to do the amsame thing for column R, T, V, X and Z however the list it is supposed to have is the Toll Charges.
Thank you for your help.
That is perfect. Works a treat. Thank you.
Are you also able to do the amsame thing for column R, T, V, X and Z however the list it is supposed to have is the Toll Charges.
Thank you for your help.
I'll look at in the morning but I'm sure I can do what you need.
Currently you have the suburbs showing up in column M via hidden values in column AF and the user must choose a value from the dropdown list. Should the value chosen in M always be the same as what was chosen in E or F? If so which one should it equal and would you like M to be filled in via a formula rather than forcing the user to select a value?
What are the rules for R, T, V, X and Z? In other words what should appear in R13, T13, V13, X13 and Z13 when that row is being entered?
What are the rules for R, T, V, X and Z? In other words what should appear in R13, T13, V13, X13 and Z13 when that row is being entered?
ASKER
Hi MartinLiss,
Column E, F and M should receive their value from the list that is found in worksheet "Zones + Tolls" Column A
The hidden values that are found in Column AF, I had there as I did not know how to create a proper dropdown list. With your coding, the values in Column AF will be deleted and not used anymore.
Column E, F and M often will have different values inserted into them, so they do not need to be equal.
Column M again will often have a separate value to column E or column F to determine the charge. The value in Column M is based on the following factors and they are ...
I hope the above makes sense.
With regards to column R, T, V, X and Z, the list of values that should appear there can be found in the Worksheet "Zones + Tolls" column D and column G.
I hope this makes sense.
The values found or to be used are all in the worksheet "Zones + Tolls".
I hope this all makes sense.
Column E, F and M should receive their value from the list that is found in worksheet "Zones + Tolls" Column A
The hidden values that are found in Column AF, I had there as I did not know how to create a proper dropdown list. With your coding, the values in Column AF will be deleted and not used anymore.
Column E, F and M often will have different values inserted into them, so they do not need to be equal.
Column M again will often have a separate value to column E or column F to determine the charge. The value in Column M is based on the following factors and they are ...
1.
If column C contains the letter E, THEN2.
Column E has the value INTERNATIONAL or DOMESTIC, then the value in column M will be equal to the value in Column F and the charge will be based on the value of column F. ELSE3.
If column F has the value of INTERNATIONAL or DOMESTIC, then the value in column M will be equal to the value in column E and the charge will be based on the value of column E.I hope the above makes sense.
With regards to column R, T, V, X and Z, the list of values that should appear there can be found in the Worksheet "Zones + Tolls" column D and column G.
I hope this makes sense.
The values found or to be used are all in the worksheet "Zones + Tolls".
I hope this all makes sense.
ASKER
Oh one more thing. When all this is done, I often hide the worksheet "Zones + Tolls" and password protect it, so that no one alters the values.
I am not sure if this would affect your coding., but thought I should let you know.
I am not sure if this would affect your coding., but thought I should let you know.
I'm confused about columns E and F having the value of INTERNATIONAL or DOMESTIC. I thought that E and F came from the Suburbs list which doesn't contain those values.
I'm also confused about using columns D and G from the Zones + tables being used as the source for for columns R, T, V, X and Z. How do I know if I should use D or G?
And finally, good news:), hiding Zones + Tolls won't affect the code.
I'm also confused about using columns D and G from the Zones + tables being used as the source for for columns R, T, V, X and Z. How do I know if I should use D or G?
And finally, good news:), hiding Zones + Tolls won't affect the code.
ASKER
You are correct that columns E and F come from the Suburbs list in the worksheet "Zones + Tolls". The reason we don't have the values INTERNATIONAL or DOMESTIC in the Suburbs list, was because they were not names of suburbs. So originally when we were using this template, that I created, we were simply typing the words INTERNATIONAL or DOMESTIC, so that we can see what to charge and how to charge each particular transaction. With your new coding, we can definitely add the words INTERNATIONAL and DOMESTIC in the Suburbs list if was is to work as previously described. I wouldn't have a problem with this.
With regards to
The more complicated manual version of this, that we use at the moment, prior to your assistance is that when Column E has the value INTERNATIONAL or DOMESTIC, then a value must appear in column R to calculate the correct charge. If Column E does not have the value of INTERNATIONAL or DOMESTIC in it, then column R is to be blank.
I have attached an image of an open invoice for you, to see how it all works. For privacy reasons I have blacked out costs and names.
Unity-Invoice-Template---showing.jpg
With regards to
using columns D and G from the Zones + tables, I would be happy for columns to R, T, V, X and Z to show the values of both lists regardless. At the present time, the way I have set it up is quite simple. Column R only uses Column G for its value list and Columns T, V, X and Z only use column D for its value list.
The more complicated manual version of this, that we use at the moment, prior to your assistance is that when Column E has the value INTERNATIONAL or DOMESTIC, then a value must appear in column R to calculate the correct charge. If Column E does not have the value of INTERNATIONAL or DOMESTIC in it, then column R is to be blank.
I have attached an image of an open invoice for you, to see how it all works. For privacy reasons I have blacked out costs and names.
Unity-Invoice-Template---showing.jpg
ASKER
The image will also show you how the column E and F work with regards to the values of INTERNATIONAL and DOMESTIC.
Okay, I think I now understand. I'll post again if I have more questions.
ASKER
No problem, I sincerely appreciate your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works perfect. Definitely understand all that you wrote.
Love the work done.
I take it that with the validation, you are referring to those steps of the Words INTERNATIONAL and DOMESTIC etc.
Happy to create a new questions.
As I have said many time on experts-exchange. The quality of expertise and easy in assistance, we should be able to provide you with a monetary tip for your efforts. Time given should be valued. We pay for a subscription to experts exchange, but to the real workers, like yourself, I often feel we are not appreciating or being able to thank you for your time.
Once again, thank you sincerely.
I have created a new question with the Title "Validations Required on Excel Spreadsheet"
Love the work done.
I take it that with the validation, you are referring to those steps of the Words INTERNATIONAL and DOMESTIC etc.
Happy to create a new questions.
As I have said many time on experts-exchange. The quality of expertise and easy in assistance, we should be able to provide you with a monetary tip for your efforts. Time given should be valued. We pay for a subscription to experts exchange, but to the real workers, like yourself, I often feel we are not appreciating or being able to thank you for your time.
Once again, thank you sincerely.
I have created a new question with the Title "Validations Required on Excel Spreadsheet"
ASKER
Absolutely amazing EXPERT. Able to complete the job with ease, easy to understand and very efficient. What a great guy.
Thank you for the kind words and I'm glad I was able to help.
Marty - MVP 2009 to 2012
Marty - MVP 2009 to 2012
01-Invoice-Template-blank.xlsm