Searchable drop down list

Question

I want to search a very long list by typing characters and find the item without having to scroll thru a 1000 items. I have this set up using vlookup and it works but its tedious! I have read there are ways using the combo box and other macro solutions but they seem to only apply to Windows versions, can I do this on the Mac version? I am using Excel 2016 for Mac.

Answer 1

Hi Mike,

Based on my test, you may try the following steps to see if you can achieve the requirement:

Assuming G column is the data column, A column is the drop-down list.

1. Input =IF(ISNUMBER(SEARCH($A$2,G2)),MAX($F$1:F1)+1,0) in F2 and update the column with the formula.
2. Input =IFERROR(VLOOKUP(ROWS($J$2:J2),F:G,2,FALSE)," ") in J2 and update the column with the formula.
3. Input =OFFSET(J2,,,COUNTIF($J$2:$J$30,"?*")) in I2.
4. Go to A2 and use Data Validation under Data tool ribbon. In the Source option, choose J column.

     5. Then when you input a letter in A2, there will be some options listed in drop-down list.

Moreover, you can refer to the file that I have sent to you via Private Message.

Please feel free to come back if you have problems in these steps. We will be glad to help you.

Best Regards,

Rodney

Answer 2

Hi Mike,

Thanks for the detailed descriptions.

As it’s a formula related question, please allow us more time to find the solution. Meanwhile, let’s welcome community members to share the great suggestions with us.

Thanks for your understanding and patience.

Rodney

Answer 3

This is close! Maybe I am doing something wrong but I cannot get it to function when I put the data validation cell on a different sheet. Also the drop-down list cannot be locked to a specific cell. It has to fill down a column. I set my worksheet up with a minimum of 500 lines. Each line needs this search function in three different cells of each line.

Here is my problem in a little more detail. I have a list of goods and services (999 at last count), a list of Vendors (1573), a chart of accounts (305), and banks (5). I am creating a worksheet such that a person can select from drop-down lists a vendor and then an item which then auto populates the description, the amount is manually entered, and then the correct account and bank selected from other drop-down lists. This is repeated for each item in the purchase from that vendor. The entire transaction and all its lines (there could be from 1 to 40 or more lines per transaction) identified by an auto generated unique number. When the next transaction is entered: new vendor and items etc... a new unique reference number is created (each transaction numbered sequentially).

The reason I am doing this and not just entering the transactions into Quickbooks, our accounting software, is security and control. Quickbooks does not offer sufficient permissions control to separate out and exclude say payroll from the expenses. The person doing the entering is in the Philippines and oversight is in the US.

The above worksheet and its columns are then mapped to Quickbooks and the data imported weekly.

I need consistency in spelling, format (such as dates), completeness and arrangement of information.

I have completed all of my requirements and the worksheet is done and it works. However it is onerous! Selecting from these long lists makes the worksheet unusable. I need a Google-like search for each of the cells for vendor, item, and account on each line of the transaction.

I realize this is no small task and is outside of my skill set. Getting all the other requirements setup did not take that long to do - this problem I have spent many hours researching and testing ideas. If this is just not possible with Excel then I will move on to a different solution.

If anyone has the skill to help me with a solution for Excel 2016 for Mac, if it is even possible!, I would really appreciate it. Maybe if this only works in Windows I can also implement in that OS if needed.

Answer 4

I'm not really clear on your intent, but based on the limited description it seems like you may be trying to find an overly complex means of executing a relatively simple task :-) If you're simply trying to locate records in a list range which meet certain criteria, why do the standard Filter or Find features not provide what you need?

If they fall short of providing the solution, please give a more complete explanation of your need as well as sample data that illustrates the nature of your list. Details & specific information will better enable someone to offer useful suggestions.