+ Reply to Thread
Results 1 to 89 of 89

Find matching text as you type from a list in a drop-down box

  1. #1
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Find matching text as you type from a list in a drop-down box

    I would be very grateful if someone could help me with a solution for the issue outlined below.

    I have a list of names in cells F3:F945 in a worksheet called ‘index’.

    On another worksheet (in the same workbook) called ‘Macro Moths’, as I type into cell D3 I would like a drop-down box to appear containing suggested names from cells F3:F945 in the ‘index’ worksheet.

    I would rather not be involved with using code; if possible formula would be better in this instance.

    I am using Excel 2003, thanks.

    Hawkmoth1

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Read here Auto Complete Typing in an Excel Data Validation List. Maybe it will help

  3. #3
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    I'm afraid that your link suggests creating a list above D3 in the ‘Macro Moths’ worksheet, then hide the rows, this isn't practical for my setup, I would like to keep the list of names on the 'index' worksheet and source them from there.

    Hawkmoth1

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    But you can copy list and put it above DV.

    I can't say more because I can't see original file

  5. #5
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    Yes, I could do that, but doing that would only give me the 'Auto complete' function, which isn't quite what I need.

    I actually want a choice of options (from F3:F945 in the ‘index’ worksheet) to appear in a drop-down box as I type into D3, from which I can select and enter one name.

    Hawkmoth1

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Look for VBA solution

  7. #7
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Thanks for your help sandy, but I would rather try to do this using a drop-down box and formula if possible.

    Does anyone else have any suggestions?

    Hawkmoth1

  8. #8
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Just though I would re-post this in case anyone who hadn't seen it earlier can help, thanks.

    Hawkmoth1

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Could you attach a sample file?

  10. #10
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    Yes, I would like to attach the file, but for the life of me I can't see how to do this - any advice please!

    Hawkmoth1

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    HOW TO:

    Attach a sample workbook. (Not a picture!)
    Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
    Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
    Remember to desensitize the data.
    Note:
    • Please do not attach password protected workbooks/worksheets
    • Please do not attach file(s) from exterior servers
    • Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

    20 rows of data is enough (probably)

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

  12. #12
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    I've just seen that you have to select 'Advanced' to insert a file - I suppose that this could be made a little clearer!

    File attached - you will see that I've put some notes (in red text) on the macro Moths worksheet, I hope that these are OK
    for you.

    Hawkmoth1
    Attached Files Attached Files

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Ok, I got att. but why zip?????


    EDIT:
    OK, I c why

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Did you read post #11 ?

    Quote Originally Posted by sandy666 View Post
    • Please do not attach password protected workbooks/worksheets

  15. #15
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    No, I'm afraid our posts crossed. If you need me to resend it please let me know.

    Hawkmoth1

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I am a student sorcerer so password is nothing for me but next time remeber that: no passwords, no autostart macros and some other things

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Sorry, but there is a VBA code that uses the same range that you want to use as a search. You said that in your file you do not want any VBA. So?

  18. #18
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    OK, thanks for the tips on post 16, I'll try to remember these in future.

    Yes, I had to compromise with using one bit of code to achieve one certain goal, but I'd rather not use any more. Are you saying that because the range I want to use is already used in the code I have, that what I'm asking for now can't be done - if so do you have any other suggestions?

    I'm sorry if I seem to be a bit green, but much of formula (and code) that the program uses has been worked out by other people on the forum, over time. My actual knowledge of all this is very limited.

    Hawkmoth1

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Just testing, be patient
    But you'll need to format a few things again because there is a big mish-mash and I remove all formats (because my eyes got a hangover)

  20. #20
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Ok, check it if it will work for you (I hope so) and it is what you want.

    Excel rules:
    1. Don't use merged cells except if someone want to kill you
    2. Don't use bells&whistles like formatting before you check all is works correctly
    3. Don't use structured tables if you don't know how it works.
    4. Try to use short names sheets that are not at the same time the function name as it is very confusing (eg. INDEX)
    5. Short names sheets = short formula, less errors, more understandable and user friendly

    of course you can do what you want
    Last edited by sandy666; 02-28-2017 at 11:49 AM.

  21. #21
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666,

    Thanks for having a go at this, it's much appreciated.

    Regarding the points you've made above in post 20 i.e. Excel rules - The reason I sent you the recording program as it was, was because it's actually in use, I am just looking at ways of improving it.

    What you have done is along the lines of what I require, but there are a few things which need to be refined.

    1) The first thing I've noticed is that the formula you've entered in column 'D' of the Macro Moths worksheet includes species from G3:G2541 on the 'index' worksheet, it should only be G3:G945.
    2) I see you've added another column on the 'index' worksheet, column F - I presume that this is some form of 'helper' column, could this possibly go after the current column 'M' rather than where it is now.
    3) When I start to type into D3 of the Macro Moths worksheet a complete list of species appears below, do these have to be visible?
    4) The drop-down box which appears when the filter arrow is clicked is great, but is there any way that the box could appear automatically, say after two digits have been typed and then for the number of the suggest species in the box to reduce as you type.
    5) As you type into D3 this should reflect what appears in the drop-down box, with the letters in that order. E.g. if you type in 'Brown' you should get all names beginning with the word 'Brown', other species with the name 'Brown' in it, such as 'Dusky Marbled Brown' should not be included in the list.
    6) You may have noticed that species were added to the Macro Moths worksheet using a 'List' as this automatically generated formula in columns A, B, C & E as new species were added and of course whatever you do for me in column D would also have to work like this.
    7) Could you please add CHAR187 into the formula into D3 of the Macro Moths worksheet, so that the double chevron is visible in the cell before you start typing.

    I'm sorry that there are so many points above which have to be sorted out, but when dealing with biological recording, everything has to be just right and the program has to be easily used by the recorder.

    Hawkmoth1

  22. #22
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    ad.1 done, but will work for defined range only. If you add something more than 945 it will not work
    ad.2 You can hide this column or you can move it to M but you need change references suitable. But done
    ad.3 start type in D3 and hit enter or click on drop down arrow. Before Enter or Click list below D3 is visible but after you will see selected only.
    ad.4 see ad.3 or look for VBA solution
    ad.5 you not have mentioned it in your description before but what if you want to find Brown on the third position in the string and if you dont know first two words? However, look for VBA solution
    ad.6 and it works. I don't know logic of your project so you need to define (or not) whatr you want. Now defined is one formula in A3, B3, C3 and E3. I didn't touch anything except 3 places: Macro Moths D column, Index M column (now) and defined NAME in Name Manager - that's all.
    ad.7 You need VBA because if you type something in D3 you will delete CHAR(187) and it cannot be back without VBA.

    Most of your needs requires VBA solutions not formula so look at Excel Programming / VBA / Macros

    regards
    Last edited by sandy666; 03-01-2017 at 09:21 AM.

  23. #23
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    Many thanks for the attached file and changes - unfortunately I shan't be able to study it for a couple of days as I'm going away - I'll get back to you on Friday.

    Hawkmoth1

  24. #24
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    No problem
    But if it will work for you don't forget to tell me about it
    thanks

  25. #25
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    I've tested the file out this morning and the points below are what I've noticed:-

    Before I tested out what you've done I first of all had to create a 'list' from A1:V3 on the Macro Moths worksheet as this is how the program is used, so that data fills in automatically each time a species is added into column D.

    1) When I start to type into D3 of the Macro Moths worksheet, everything works well, the drop-down box appears and I select a species from the list, but as soon as I make the next entry into D4, although the drop-down arrow is present, it doesn't work and no suggested options are given.
    2) Also, I still don't quite understand why a full list of species is visible below D3, as I really only want the suggested options to appear in the drop-down box, it it possible to rectify this.
    3) Finally I don't quite understand your answer as regards adding CHAR(187) to the formula in D3 as this has already been done to existing formulas in A3, B3, C3 & E3 without using code, and it works very well.

    I do understand that some of my points in post 21 can only be done using VBA, but for the time being I'm trying to avoid using this as much as I can.

    Thanks for all your help

    Hawkmoth1

  26. #26
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    ad.1 - not true it works (see attachment)
    ad.2 - I will see what I can do but there is a few limitations. Later.
    ad.3 - because this is a Data Validation cell not cell with any formula where you can add CHAR(187) on the end or use IF(). What should be blank if you want to use IF(?="",CHAR(187),?) ?
    You can't use function directly in cell of DV whitout VBA. Try: type directly in D3: =CHAR(187), Enter. You will see chevron in this cell but if you want select (search) any of species and type in D3 word, eg. mot you will delete =CHAR(187) and it won't come back. D3 as DV uses name from Name Manager where is defined formula like this: =OFFSET('Macro Moths'!$D$4,,,COUNTIF('Macro Moths'!$D$4:$D$945,"?*")).

    I cannot attach an example because I changed the look of your project and don't want to shock you
    Attached Files Attached Files
    Last edited by sandy666; 03-03-2017 at 08:31 AM. Reason: gif added

  27. #27
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666

    OK, as regards point 1, - I don't know how to proceed as it doesn't work my end. Perhaps something has been turned off or altered, anyway I'm at a loss as to what to try to get it to work - the list of options in the drop-down box definitely becomes deactivated once the first name has been entered into D3.

    Thanks for explaining point 3.

    Hawkmoth1.

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    See attached:

    Sheet"YouTube" has links to videos explaning how this works.

    In "Macro Moths" cell E1 is used as the "=Cell("Contents"): this can be changed.

    In "INDEX" column P

    =IF(ISNUMBER(SEARCH('Macro Moths'!$E$1,LEFT($F15,LEN('Macro Moths'!$E$1)))),MAX($P$2:P14)+1,0)

    In "INDEX" column Q

    =IFERROR(INDEX($F$3:$F$945,MATCH(ROWS($Q$3:Q15),$P$3:$P$945,0)),"")

    Ignore "Circular Error" warnings; these result from result of using CELL(C"Contents") technique

    Start typing in Column D then click DV arrow.
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    To John,

    Yes, the drop-down box arrow is visible, but when I start to type into D3, the box doesn't generate with any options in it.

    ----------------------------------------

    To sandy666,

    Ad.1) Yes, I did use the VBA and I made sure that I activated it.
    Ad.2) The result of the search shows the result in D3, where it's supposed to be, but it also shows the result in D4, where it shouldn't be.
    Ad.3) Oh I see, but I've been developing this program over several years and I'm afraid I can't tell you who the original author was.

    Thanks to both of you.

    Hawkmoth1.

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Type in "Whi" then click DV arrow/button (as instructed).

  31. #31
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Peter,
    So you want type in a search box (D3), it will give you a few choices than you select one and...???
    options:
    a) it will show you below D3 all result of your choice
    b) it will give you first result, and next search you want to show next result in the next row?
    sorry but I'm confused

    If you use John's solution you have search box in every row and you need type there a few letters eg. bea than select from DV what you want and it will give you result in D column and other columns also.

    It's up to you

    I wanted to show you how it works, but....
    You need to recognize how it works by yourself.
    Last edited by sandy666; 03-03-2017 at 12:46 PM. Reason: typo

  32. #32
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello John,

    If I type in 'Whi' and hit the button, nothing happens (no box with suggestions). If I type in the complete name i.e. White Ermine then hit the button, the name loads in, but of course that rather defeats the object, doesn't it.

    Hawkmoth1.

  33. #33
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Well it works absolutely fine for me: all the entries in the file I posted were created by just typing (say) "gre" and getting list including "grey ...." and "green ..."

    I assume by "button" you mean the "DV" arrow.

    I have just typed in G in D21, clicked the "DV" button at the side of D21 and now have list of all entries beginning "G".

    If nothing is happening then have you entered/cleared E1: if so, it will not work.

    Cell E1 must have

    =CELL("Contents")

  34. #34
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    See attachment (John's solution)
    Attached Files Attached Files

  35. #35
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Thank you Sandy: you'll have let me know how you produced the GIF (not into media !)!

  36. #36
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Quote Originally Posted by JohnTopley View Post
    Thank you Sandy: you'll have let me know how you produced the GIF (not into media !)!
    But not here, I think

  37. #37
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello both,

    I have just seen the video that sandy666 sent me and that's exactly what I I've been doing, but no box or list appears when I hit the down arrow and actually it's a very similar problem I'm having with both your solutions.

    Just a thought, could it be that I've got something 'switched off' in my excel which isn't allowing this.

    Hhawkmoth1

  38. #38
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    If you have "slow" computer it can be reason. Maybe you should wait a few....
    It's hard to say if I don't see what are you doing and I don't know what you have change there....

  39. #39
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I think you should attach latest version of your project

  40. #40
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666,

    Well, you actually have the latest version of my project - it's the first file I attached.

    You should also know that my computer isn't slow.

    Just another thought (and I'm probably barking up the wrong tree here), but when you originally removed all the formatting etc to work on the file could something have been altered to affect the program working properly my end.

    Hawkmoth1

  41. #41
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I removed formats and one wrong defined table. That is all.
    Format does not affect how the "our" solutions work.
    Format is only bells&whistles and only beautifies the look

    You can add John's or my solution to your first attached file to check it by yourself.
    Last edited by sandy666; 03-03-2017 at 02:48 PM.

  42. #42
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    The file I used was the one Sandy posted with his solution.

    I removed the formula from column D4 down and replaced the DV list the "Search_list" rather than "SLIST". "SLIST" still exits as a named range.

    And added the formulae in INDEX to create "Search_List" PLUS adding the =CELL("Contents") in E1.

    I assume Sandy's video used the file I posted so unless you transferred the "solution" to another file, there is no reason why it does not work.

  43. #43
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Exactly what I did

    Quote Originally Posted by sandy666 View Post
    See attachment (John's solution)

  44. #44
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Here is original (unprotected) file. I added John's solution.
    Two defined Tables: Table1 and Table2 was removed.
    No less, no more...

    edit:
    UPS, I lied I changed IFERROR() to IF(ISERROR( because of Excel 2003
    Last edited by sandy666; 03-03-2017 at 03:32 PM.

  45. #45
    Registered User
    Join Date
    05-21-2014
    Posts
    92

    Re: Find matching text as you type from a list in a drop-down box

    Hawkmoth1,
    Check to see if you are in manual calculation mode. You need to be in automatic calculation mode (or press F9 each time to calculate within manual mode) for the dropdown to work.

  46. #46
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello All,

    Great news, I opened up the latest file sandy666 sent me in post 44 and the drop-down box along with suggested options now seems to be working fine - it seems like the changing the IFERROR() to IF(ISERROR(, probably caused the program not to work my end. I must say, you guys are like a dog with a bone trying to sort out everyone's Excel problems - great job.

    There is just one 'tidying up' issue on the Macro Moths worksheet that I would ask to be fixed please. When I start to type into D3 the drop-down box appears in column D, but for all other entries (D4 downwards) the drop-down box appears to the left hand side of the screen. Is it possible to make the box appear beneath each entry in column D, thanks.

    Hawkmoth1

  47. #47
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Peter,
    Sorry, You need to wait for John because I don't understand what you want
    For me every row in column D works the same.

  48. #48
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Nor do I understand!!! ?????


    Drop-down box ALWAYS appears Under the selected cell in D (and as I don't have 2003 version) I cannot test.

  49. #49
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Maybe take snapshot of these two situation and give us as example?

  50. #50
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    OK, here is the requested screen shots.

    Shot 1 shows the drop-down box correctly beneath D3 as you enter data -
    Shot 2 shows the box to the left of the screen once you type in D4 or below - and actually I don't know if this helps but, when I hover over the options in this box, instead of an arrow which I get with 'shot 1' above I get a white cross.

    Hawkmoth1
    Attached Images Attached Images

  51. #51
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Could you tell me what is it, this blue frame around few rows?
    Did you defined Tables again?

  52. #52
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    I cannot reproduce this and from your image you do not appear to get the DV arrow when selecting D4,

    If you click on D4 (or any cell in column D) you should immediately get the DV arrow (before entering any data).

  53. #53
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Just in case see attachment
    Attached Files Attached Files

  54. #54
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    To sandy666 - the blue line is there because I've created a 'List' around A2:T3, this is the way I get data to generate automatically from the rows above (in the greyed out columns) each time I add anew species in column D.

    To John - Yes, I do get a DV arrow in the correct place when I click in any cell of column D, it's when I click on the arrow that the drop-down box opens to the left of the screen, as in shot 2 in post 50.

    Hawkmoth1

  55. #55
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I think you have special "species" of excel

  56. #56
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    I don't understand the "list" A2:T3 ???

    A,B,C and E are automatically filled using the INDEX formula: F onward is manually entered (?)

  57. #57
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    To sandy666 - I've just looked at your attached file in post 53, the video shows exactly what I'm doing.

    To John - Re the "list" in A2:T3 - I wonder if you're not understanding this because of my older version of Excel, either because it's not available or called something else in newer Excel versions. Anyway this is what I do - I go to Data > List > create list, enter cell reference, then any Formula which is in row 3 will automatically be copied when I type into D4, D5 and so on, I hope this helps you.

    Hawkmoth1

  58. #58
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Quote Originally Posted by Hawkmoth1 View Post
    then any Formula which is in row 3 will automatically be copied when I type into D4, D5 and so on
    Remove this definition of list and try again with Data Validation.
    With this list you duplicate your action probably
    or
    define two lists
    from A to C and from E to T (but this is nonsense a little)

    Because there is formula below last selected DV your table is not neccessary
    2017-03-04_143353.jpg
    Last edited by sandy666; 03-04-2017 at 09:36 AM.

  59. #59
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    First, column D does not have a formula other than that from the DV definition.

    For other columns, (A,B,C, and F) simply drag the cells from row 3 down the column to copy all formulae ( as per ALL the files that have been posted).

    As you enter data manually in F to T, any formulae, should they exist, will be over written.

    So I can add no more to this: as far as I can judge there nothing in this solution which will prevent it working in 2003 (as it works fine in compatibility mode).
    Last edited by JohnTopley; 03-04-2017 at 09:48 AM.

  60. #60
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    To sandy666 - sorry, I'm not quite sure what you're saying here - can you please tell me what I should do to to use Data Validation instead of a list as I haven't used this before, thanks.

    To John - When I attached the original file (see post 12) the list was active, so I thought everyone knew I was using it. I don't really want to 'drag down' formula as the automatic loading of formula using a 'list' is much more tidy and far less reliant on the user forgetting to do this or getting it wrong.

    Hawkmoth1

  61. #61
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    In the first (your version) Table exist but in the latest version Table(s) are not neccessary
    because there is formula(s) which doing the same. If you define Table again it will duplicate action so probably you see weird things.
    What you can do:
    1. remove Table
    2. see attachment
    Attached Files Attached Files

  62. #62
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Just another update - I've just done some more testing - if I remove the 'List' by 'converting to range', the Drop-down box works perfectly every time data is entered into column D. However, I would really like to keep the automatic entry of formula into the greyed out columns, I wonder if the option which sandy666 suggested would do the trick?

    Hawkmoth1

  63. #63
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    If you dragged the formula down 1000 rows, I doubt it going to be missed!

    The "list" function you describe is not available in this form in later versions of Excel: The equivalent appears to be the "Table" function (of which I am not a particular fan anyway but its "redeeming" feature is the ability to propagate formulae).

  64. #64
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    It doesn't matter if you convert Table to range or simply remove the table.
    Removing/converting Table turning off duplicate action and only one thing stayed on - formulas from row 3 to 1000
    (if you drag them from row 3 as far as you need , of course )

    You cannot use two different things to the one action. It's like you want eat cake and have cake in the same time
    Last edited by sandy666; 03-04-2017 at 10:25 AM.

  65. #65
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Thanks guys - I can see what you've both saying but, what I don't understand is, when using the 'List' feature, there must be a reason why the drop-down box D3 always appears correctly when data is entered into D3, but not in a subsequent rows, I am definitely a little confused.

    I really like the function of propagating formula automatically, as this keeps formula down to a minimum and therefore keeps the size of the file down, which is very necessary in my situation, as these types of recording programs can get very large in some cases.

    Hawkmoth1

  66. #66
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Delete unnecessary formatting, the file will be smaller
    Delete unnecessary tables, the file will be smaller
    Leave only what is really necessary, the file will be smaller
    Choose what you want: tables or formulas. It's up to you
    We did all to solve a problem and IMHO problem is solved.

  67. #67
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    @Sandy: thanks for all your help with this.

  68. #68
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    John, I think I should say Big Thanks to you

    btw. I don't see any PM from John, about gifs

  69. #69
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello Guys,

    A massive thank you to both of you, I'm only sorry that my older version of Excel caused so many issues for you both.

    I think that given the circumstances you have solved the issue and I shall mark this post as such, I will also click on both your reputations.

    But if you're both not totally fed up with me could I possibly ask you to do one final thing - when I started this post I only asked for the fix on the Macro Moths worksheet, but actually I needed the same thing doing on the Micro Moth worksheet as well. I have tried to do this myself, but I can't get it all to work. As with the Macro Moths the source cells is on the 'Index' worksheet and these are E947:E2541 and L947:L2541.
    The only potential issue I can see here is that there is a lot of duplication between these two ranges, but I have to include them both as the columns contain both new and old names of the same species and both which are valid and therefore would have to be included in the drop-down box options.

    If for any reason you can't do the above and you would like me to start a new post, I'll fully understand.

    Hawkmoth1

  70. #70
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Give the last your working version. I won't looking and download which one is correct or not.
    Is structure of Micro the same like Macro?

    btw. You can do this by yourself, simply duplicate all from Macro (in name manager also) and change references.
    I assume the Micro and Macro structure is similar

  71. #71
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666, well as I've said, I have tried several times to do this to the Micro Moths worksheet, but I'm afraid that it's defeated me.

    You will see that the structure of the Micro Moths worksheet is slightly different. On this worksheet I enter the Latin species name into column E3 downwards. There is also another feature on this worksheet (which isn't on the macro Moth worksheet), that is, if an old species name is entered, the user is asked, by way of a box if they would like to replace the old name with the new one - this is what the one VBA code in the program is used for.

    Hawkmoth1

  72. #72
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Macro was a one problem
    Quote Originally Posted by Hawkmoth1 View Post
    I think that given the circumstances you have solved the issue and I shall mark this post as such, I will also click on both your reputations.
    Now is the second problem
    I will look at on Mon or Tue because I need one day of holiday (family).
    So if someone will do that earlier will be fine.

  73. #73
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Check if it will work for you

    edit: file was changed, now is ok
    Not ok, wrong range look at next latest ver
    Last edited by sandy666; 03-04-2017 at 05:10 PM. Reason: change file

  74. #74
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Having 2 lists is going to create a real problem with a single Data Validation as to which list ( E or L) you use UNLESS you combine them into a mixed list (old and new Latin Names): then you have the problem of offering the name change,

    Can all be done but rather "messy".

    You need to think very carefully how you want this handled.

    I have changed a workbook to do the "Micro" DV as per the "Macro" but just working from Column E.

  75. #75
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I think John (as native english speaker) understand better what you want, Peter.
    So I will stay on side

  76. #76
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    @Sandy: see my previous posting ...and you need to change E range in your posted file to E947 onwards.

  77. #77
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Something like this, John?

    I wonder why there are two lists in one column if excel has over 16k columns?
    Last edited by sandy666; 03-04-2017 at 05:56 PM.

  78. #78
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hi Guys, I see you've both been bust this morning.

    To sandy666 - I have tried your using your last attached file in post 77 and the drop-down box shows all options some of the time, but on other searches, options which should be there are missing. E.g. I typed in 'Euc', this should have given me a fairly good list of options but the only option which was shown was 'Euclemensia woodiella'.

    To John – Re post 74, OK, I see what you mean about two lists. As you say, combining the lists in columns E and L would cause a problem with the ‘name change’ feature. Is there anyway you could copy the two existing lists in column E and L, amalgamate them into a single new list, say in an unused column (N or O), remove all the duplicated names and then use this list for your search options for the drop-down box, could this work?

    Hawkmoth1

  79. #79
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    Do you want something like this?

  80. #80
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    Merged columns E & L into N, removed Duplicates and sorted (makes checking easier).

    Attached is updated Sandy's last posted file.

  81. #81
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    or like this one

  82. #82
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I didn't check your version, John but I think we did the same

  83. #83
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello Guys - I've just been testing the attached file in post 80 and it's all looking and working very well indeed.

    There is however one glitch I've noticed as regards giving the correct options in the drop-down box (there may be others). When I type for example, 'Epi', the top option in the list is for a species starting with 'Eph' and this is reflected in S3 of the 'index' worksheet.

    Hawkmoth1

  84. #84
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    check this one

  85. #85
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666 - in your last attachment I see you have combined the data in columns E and L into one column of data in column E. Unfortunately doing this has caused an issue with the ‘name change’ feature and it no longer works. Also I need the data to be kept in two separate rows so that I can manually alter any names which change in the future.

    John almost sorted the issue out with his attached file in post 80, but there was just one slight glitch which I've mentioned in post 83.

    Hawkmoth1

  86. #86
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Find matching text as you type from a list in a drop-down box

    See attached (still not clear to me why the "problem" occurred).

  87. #87
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    I think we may have finally nailed this with the file John attached on post 86 - I'll just do a bit more checking, then I'll get back to you later.

    Hawkmoth1

  88. #88
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Find matching text as you type from a list in a drop-down box

    Hello sandy666 & John,

    After a lot of testing, typing in random 'start letters' I can now confirm that the last file which John sent me in post 86 has indeed done the trick and everything associated with the drop-down box seems to be working perfectly. I'm so grateful to you both for all the effort you've put into this mega post, you've done an excellent job for me.

    My project of course carries on, and I shall probably be back on the forum at some stage or other asking for more help again with my recording program - what would I do without you!

    Good luck to you both, I shall now mark this post as solved and tick on both your reputations.

    Hawkmoth1

    P.s. After giving it a lot of thought I have decided not to carry using the 'List' feature as you both advised me - I shall just drag down the formula in the appropriate columns.

  89. #89
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find matching text as you type from a list in a drop-down box

    I am glad it works for you b0242.gif
    Last edited by sandy666; 03-06-2017 at 08:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can't type in cell with drop down list
    By TRazzo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-23-2016, 02:24 PM
  2. [SOLVED] Drop down list and matching drop down selection to other data
    By connormg in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-06-2015, 02:18 AM
  3. Replies: 1
    Last Post: 09-28-2013, 03:45 PM
  4. Replies: 2
    Last Post: 04-11-2013, 11:14 AM
  5. [SOLVED] Search text in cells, find matching text based on list, remove all but one entry
    By adam_mc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2013, 12:50 PM
  6. Drop down combo box list on type
    By sick stigma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2009, 09:12 AM
  7. drop down list that i can also type regular text in as well
    By Joyce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1