Forum Discussion
Drop down list - highlights cells
Hello Justin_Blush,
Is it possible to share a sample of the data that you are working with? That would be a tremendous help for me to find you a solution.
I am trying to categorize different information related to U.S. policy and utilizing the vast majority of the cells to list quotes from government documentation from a variety of different agencies and sources. There are different categories of U.S. policy that I am wanting to highlight i.e. Homeland Defense, Federal Law enforcement, Border patrol, and social programs.
The categories will be listed in the drop down box and preferably be designated by a particular cell color. So if I select “homeland defense” (green) then all the cells with quotes that have keywords such as: defense, military, homeland, adversary, weapons, army, navy, etc. will highlight green, so as to quickly locate supporting documentation and quotes that align with homeland Defense.
Ultimately, I would like to have multiple drop downs with more refining categories to further isolate information. For example government funding sources and authorities so to isolate supporting documentation that takes into account not just the policy but also what organizations are involved and the funding sources.
Hope that makes sense.
Attached is a sample file so that you can follow along. I hope this is what you're looking for...
- Create individual tables with headers as topics of U.S. policy (replace each space with "_")
- Under each table, enter key words that you wish to find for each topic
- Individually name each table the same as headers
- Create a data validation table as a list from each of the headers.
- Create a table full of government documentation and quotes.
- Select upper left most cell of table and create Conditional Formatting. Use the formula:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),A14)))>0
- In this formula, $A$11 is the cell with data validation list and A14 is first cell of table.
- Format fill color to green.
- Apply conditional format to entire table range
If you have any question, please do not hesitate to ask.
- For some reason I am having cells highlight that dont include keywords listed and conversely text that has the keywords doesn’t highlight. When I choose different categories there is a visible change in highlighting but similarly it’s unclear by what metric it is occurring. Is there a way to see how it’s organizing those decisions?
Its very close and will work great when I overcome this error.Could you share your updated workbook so that I can search for the issue? (please remove any sensitive information)
I figured it out, thank you. This works well.
One more question. Lets say I wanted to have another filter added. Another dropdown box that isolated which "description" columns to search for the keywords. For example on your sheet you have description, description2, and description3. Say particular "descriptions" were useful to certain government agencies/organizations i.e. Army, Navy, FBI, CIA, etc. Each agency might have multiple description columns that were useful, but no agency would need information from all the description columns. So if I had another dropdown box for the user to isolate an agency and the respective useful description columns for the other dropdown box (homeland_defense, federal_law_enforcement, etc.) to search for the keywords.
Hopefully this makes sense. Thank you again. This is the last part for this project I would hope to achieve.