Forum Discussion
Solved
Drop down list - highlights cells
I have a drop down list in excel with 4 categories. I want a user to be able to make a selection and based upon the selection made have cells throughout the workbook highlight if certain keywords exis...
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.
Hello Justin_Blush,
Based on your description, it sounds like what you need is a conditional format. However, I cannot offer a specific suggestion without a more detailed example.
- Ok here is the example.
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.
- Create individual tables with headers as topics of U.S. policy (replace each space with "_")
