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?
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?
