Products (Sheet) has all products in Column B6:B200
Purchases (Sheet) C5:C500 are all Drop down boxes that link to data in Products B6:B200, I can manually scroll and select an item but would like to be able to type the first few characters and have only matches show up in drop down box.
It's not possible to do it in Data Validation
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Does something like this work for you?
Sorry for off-topic interjection:
@Croweater
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Last edited by AliGW; 07-30-2020 at 05:06 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@Croweater, it does not work
It works for me...type a (partial) name in B2, then use the dropdown (in B2) to reduce the list in D2:D20
It works fine, but without an explanation of how you did it, it's not much use to anyone.
If you unhide the helper columns I've hidden (just to be tidy), it will show how it's done.
It searches the list of names for the partial match entered to create a variably sized helper list
Then you count the number of names in the new list, name the list (using the count of the number of items as the number of rows in the list) and use that in the data validation.
Last edited by Croweater; 07-30-2020 at 05:39 AM.
Can you please share the formula and locations thereof in a post? I know how to back engineer solutions, but many members would not know where to start. As I have mentioned to you before, some will not be able to download your example file, so a step-by-step explanation would really help to benefit the whole community. Thanks for your co-operation.
Last edited by AliGW; 07-30-2020 at 05:43 AM.
In C2:C20
=IF(ISNUMBER(SEARCH($B$2,D2)),MAX($C$1:C1)+1,0)
This puts a (progressive) number against each item where there is a partial match
In F2:F20
=IF(ISERROR(VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0)),"",VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0))
This uses a row number to do a vlookup in the range C2:D20 to create a (variably sized) list
Then you create a named list with this as the source...
=OFFSET($F$2,,,COUNTIF($F$2:$F$19,"?*"))
The COUNTIF is the number of non blank items in the helper list. The OFFSET function allows you to use the variably sized list in the named list in the data validation.
The same topic was discussed yesterday and the link is given below:
https://www.excelforum.com/excel-gen...ml#post5372295
You need to turn the error alert off in the Data Validation.Originally Posted by torachan
@Croweater, it does not work
Croweater, I believe I have asked you in the past to not just post a file, but to include an explanation on your post as well. It's not too much to ask, so please do so
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks