Dropdown that filter by 1 word in a cell

Registration date
Thursday February 24, 2011
Last seen
February 24, 2011
 Estall -
I'm working on Excel 2003 and I have a validation list of four things.

for example


But some of the cells may have two of the above in.

How do I make a dropdown filter for that word, but ALSO find cells that contain that word (when there may be more words as well).

Thanks in advance!

3 replies

Registration date
Sunday September 12, 2010
Last seen
October 25, 2021
Sorry Estall,

Failed to understand you.
It looks like you would like to expand your validation list, which should be no problem since you already created one?
Could you reexplain in more detail?

Best regards,
Okay, will expand!

I have a dropdown on tab#1 with some phrases on.

On tab#2, there is a row of entries and a column which has the same phrases appear in.

Back on the tab#1, there is a vlookup formula, which reads which phrase is selected on the drop-down, looks at the column in the tab#2 for that phrase and, back on the tab#1, populates the cells.
(It also looks at a unique reference to ensure which row goes where).

So, on tab#2, the column with the phrses in sometimes has multiple phrases in it!

What I want is that when, in tab#1, I select (for example) 'bearcub' in the dropdown, the formula picks up all the cells in the column with that single phrase in, but ALSO all the cells in that column where the phrase appears (when it's not the only phrase).

Does this help?

Ultimately, using a dropdown, I want tab #1 to be populated with entries where the column in question has the phrase 'bearcub' (for example), regardless of whether it's own or not.

In the column in tab#2:
cell 1 - bearcub
Cell 2 - Aircon
Cell 3 - Cliffrichards
Cell 4 - bearcub Aircon

When I select 'bearcub, in the validation on tab#1, I want to formula on tab #1 to pick up columns including cells & 4 (as they both contain 'Bearcub').
If I switch the dropdown to Aircon, the formulas should pick up 2 & 4.

I hope this makes sense!
Registration date
Thursday January 28, 2010
Last seen
August 2, 2020
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.

your data need not be to be real data but a good representative of how data looks like
Registration date
Sunday September 12, 2010
Last seen
October 25, 2021
OK Estall, it's getting more clear to me know. Just don't know what result you would like to see?:
Let's say,
Sheet 1, cel A1 contains dropdown in which you select bearcub.
Then in sheet 1, cell B1 you would like to see:
1, 4 or
bearcub, bearcub Aircon
or do you want to populate two cells like:
B1: 1
B2: 4
B1: bearcub
B2: bearcub, bearcub Aircon

Answering Rizvisa's request is also a wise move, since he can solve pretty much any query!

Best regards,
Hi, having a bit of trouble getting a document on.
I wonder if this will help, in the interim.

I can do a COUNTIF on the first column. This observes SDS wherever it appears (be it on it's own or with other words)


(I110 = SDS, say)

I can also do a SUMIF of all the rows where SDS appears in the F column that also have 'Green' in the T columns.


So, any cell in the F column that says "SDS" will be counted, but not if it says, say "SDS TP RGB" I want it to count ALL F column cells that include SDS in it.

Sorry I haven't got a document to show at this time, does this help?


I want this formula to count ALL instances where SDS appear in the column.
Okay, I've got round it.

I used this:

=SUMPRODUCT(--($S$3:$S$104="Green"),--ISNUMBER(SEARCH("CP",$H$3:$H$104)), $CS$3:$CS$104)

I just added a new column and put 1 in C3:C104.
A bit trickier than I needed, but it works.