Posts by TheRealAldyhyde

Apologies, my last post didn't quite take into consideration that the entire project list was not being taken for a match but rather just the equivalent row

This formula in the conditional formatting "=IF(SUM(COUNTIF(A2,ProjectCode)),"FALSE","TRUE")", will work for the whole project list provided you set your project code list as a dynamic named list with this formula "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)" in the named range menu

Here -

$A$2 is the starting code (in sample it was 2nd row, A Column)
$A:$A is the column containing your Project Code List

Conditional Formatting Formula explanation is as follows -

A2 - is the first cell to add into conditional formatting

ProjectCode - is the name of the Named range that I have instructed you to make earlier
IF formula is reversing the return that the SUM and CountIF formula is returning, if you don't add this then Conditional formatting will colour the matching results and not the entries that don't match as you requested

Easy solution using conditional formatting -

Use the following formula in Conditional formatting - =IF(A2=Sheet2!A2:A16,"FALSE","TRUE")

A working sample modified from your example has been added for your convenience

I am aware of the macro requirement, but wasn't able to do it myself. At min if someone can guide (or upload a version) of the following video's logic such that I can use it within a combo box (or cell) directly then that would be great as well -

I already saw this video as well and the same issue that you mentioned is the main problem as this workbook will be distributed to clients that may be using upto Excel 2003/2007 and the function mentioned works only with 365.

Forgot to mention this again in the previous reply, the number of rows in the Database Entry Sheet - Column A will keep changing as it is updated from the database every time a significant event happens (such as when a new item may be added to a client's inventory, every time the workbook is opened etc) so it needs to be a dynamic range and should thus reflect all the latest entries in the. drop down menu as well...

The example Workbook is uploaded here,

Data type is always Strings
Expected Results have already been mentioned in the original Post, ideally typing in the dropdown cell/combo box should search through the database for matches (not only just the beginning but through out the string of the database entries) ie.

If User were to type 'app' - entries such as 'app'le and pine'app'le should pop up in the dropdown

Here as we can see app is in the middle of Pine'app'le, it is still being picked up by the dropdown suggestions
Also matches should be Case insensitive

Searchable Drop Down.xlsx

Lastly the reason for not using User forms is as the last sheet of the Example Workbook shows - the user visible sheets are already very well designed and uses a combination of autoshapes and images to provide a very intuitive and for a lack of a better term 'appealing' UI/UX and I personally feel the best way to provide this dropdown without taking away from that is to use Combo boxes.

I already have macros to pass information from the Autoshapes to cells/variables in vba and parse the information to and from the database that finally keeps track of the information as well.

The problem is a part of a larger project that utilises an assortment of APIs that retrieve data from a database and paste them as required into respective columns (this part is done), the next part is to get a searchable dropdown cell/combo box ( basically anything except a user form or anything like that which takes the focus out of the excel window) that shows the user (as they type) queries that match from the respective column (populated as mentioned earlier by the APIs) from which this cell is to be validated. The matching should be able to match from not just the starting of the query but other parts as well ie,

If User were to type 'app' -

entries such as 'app'le and pine'app'le should pop up in the dropdown

Furthermore Search shouldn't be Caps sensitive either

Main Problems faced from earlier ideas

1) I checked google (and youtube) found a few solutions that go along the line of using multiple Formulas such as Search, Max, IsNumber etc (

i) This option is not dynamic (new rows added to data validation column do not reflect)

II) The Data validation Column is quite big (6000 rows+) and significant lag can be observed

2) This option seemed closer to success as it doesn't slow the system as much but it uses VBA User Forms and also is not dynamic ie. new entries to Data Validation column do not reflect (

3) Some cells additionally also require an option to allow for multiple selections from the drop down (Ideally separated by a comma so that I can directly parse it to the POST API that sends the data to the database)

4) Some other cells may require an option to allow for entering Strings that have no match as well (Basically disabling the error prompt in traditional Data Validation)

- Point 3) and 4) of the problem that can be addressed after the Initial problem is solved