Avatar of Steve B
Steve B
Flag for Australia asked on

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
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Here's a modified workbook that has code that so far only affects Pick Up Suburb and you need to double-click the cells in that column to get the autocomplete functionality. Give it a try and let me know if it's the way you want it (for that column).
01-Invoice-Template-blank.xlsm
Steve B

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?
Martin Liss

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Steve B

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.
Martin Liss

I'll look at in the morning but I'm sure I can do what you need.
Martin Liss

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve B

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 ...

1.

If column C contains the letter E, THEN

2.

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. ELSE

3.

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.
Steve B

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.
Martin Liss

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Steve B

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
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
Steve B

ASKER
The image will also show you how the column E and F work with regards to the values of INTERNATIONAL and DOMESTIC.
Martin Liss

Okay, I think I now understand. I'll post again if I have more questions.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve B

ASKER
No problem, I sincerely appreciate your help.
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve B

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"
Steve B

ASKER
Absolutely amazing EXPERT.  Able to complete the job with ease, easy to understand and very efficient.  What a great guy.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Thank you for the kind words and I'm glad I was able to help.

Marty - MVP 2009 to 2012