Searchable Data Validation Drop-Down using a FILTER() function..with a twist

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
I've just been made aware of the technique for making a drop down list with data validation searchable, using a Dyanmic Array function, FILTER().

The examples I've seen, however, are those with a single cell containing that drop-down list. And I have this technique mastered.

However....
I have an excel table, and in each cell in a particular column, I need to access the same data for Data Validation. It would be very helpful for each row to have this Searchability, and to do this, I need to write the FILTER function with a dynamic reference that responds to what row of the table I'm in.

For the life of me, I can't think of a way to get this accomplished. I really believe it can be done without the use of VBA or a Custom Function, but I may have to resort to that.

My function will look like this, with "search text" being replaced by this solution I'm seeking.
=FILTER(Table_Data[Column1],ISNUMBER(SEARCH("search text", Table_Data[Column1]))

To be clear,
I'm looking for a solution that reports the content (or the reference, if that's easier) of the row I'm operating on in my Excel table. So if I'm on row 7, it'll give me the content typed into row 7 for search purposes. If I click to row 8, it'll show the content I'm typing in row 8. It's probably easiest to create a named range to hold this value.

Am I nuts? Can this be done?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

nihad

New Member
Joined
Feb 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Can you share an example of your excel sheet using Xl2bb?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,862
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top