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. Input =IF(ISNUMBER(SEARCH($A$2,G2)),MAX($F$1:F1)+1,0) in F2 and update the column with the formula. Input =IFERROR(VLOOKUP(ROWS($J$2:J2),F:G,2,FALSE),' ') in J2 and update the column with the formula. Input =OFFSET(J2,COUNTIF($J$2:$J$30,'?.' )) in I2.
Go to A2 and use Data Validation under Data tool ribbon. In the Source option, choose J column. 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. Please feel free to come back if you have problems in these steps. We will be glad to help you. Best Regards, Rodney. 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. Please mark HELPFUL or ANSWERED as appropriate to keep list as clean as possible ☺ Regards, Bob J.
Inserting a drop down box in Excel requires a few basic steps to complete. An inactive list displays one value; a list activated by clicking an arrow button displays the whole list of values. Excel’s Data Tools give you options to display a message as the mouse hovers over the cell or show an error alert. Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel Online More. Less You can help people work more efficiently in worksheets by using drop-down lists in cells where they can pick an item from a list you create.
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.
Dropdown lists can contribute to a much more efficient and effective spreadsheet. The more complex the spreadsheet, the more useful drop down boxes can be. If you’re struggling to create one in your spreadsheet, help is at hand.
Here’s how to create a dropdown list in Excel. Also see our article There are actually a couple of ways to create a dropdown list in Excel. They both use the same fundamental steps but offer a little flexibility in how you build your list. Create dropdown lists in Excel Here is the main way you can create a dropdown list in Excel 2013 onwards.
You have to create one sheet to host the data and another sheet to host the spreadsheet itself. For example, you want the dropdown list to appear on Sheet 1 so you will add the data for that box in Sheet 2. This keeps everything separate. Type the entries you want to feature in your dropdown in Sheet 2 in Excel. Select them all, right click and select ‘Define name’ from the options. Name the box and click OK. Click the cell on Sheet 1 in which you want your dropdown box to appear.
Click the Data tab and Data Validation. Select List in the Allow box and type ‘=NAME’ in the Source box. Where you see NAME, add the name you gave in step 3. Select ‘Ignore blank’ and ‘In-cell dropdown’ as you see fit. Click the Input Message tab and either uncheck the box or add a message to be displayed once a selection is made in the dropdown box.
Click the Error Alert box if you want to make changes. Otherwise click OK. You dropdown list should now appear in the cell you specified. Give it a quick test to make sure it works. Use a table to populate a dropdown list You can also select a table to build your list.
To use a table in a dropdown list in Excel. By using a table, you are able to make changes on the fly without having to edit the named ranges.
If your spreadsheet is always evolving, this could save a lot of time. Type the entries you want to feature in your dropdown in Sheet 2 in Excel. Highlight the entries, click the Insert tab and then Table. Define the table and name it. Click the cell on Sheet 1 in which you want your dropdown box to appear. Click the Data tab and Data Validation.
Select List in the Allow box and click the little cell icon next to the Source box. Highlight the cells in the table you want to feature in the dropdown box. The Source box should then read something like ‘=Sheet2!$A$8:$A$11’. Select ‘Ignore blank’ and ‘In-cell dropdown’ as you see fit. Click the Input Message tab and either uncheck the box or add a message to be displayed once a selection is made in the dropdown box.
Click the Error Alert box if you want to make changes. Otherwise click OK. The new dropdown box should appear in the cell on Sheet 1 where you selected.
Now you have a fully functional dropdown list in Excel!