SOLVED

Drop down list - highlights cells

Copper Contributor
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 exist related to the category chosen in the dropdown list. I would like it to find multiple keywords related to the category.

Thank you in advance for your help!!
13 Replies

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.

@Justin_Blush 

 

Attached is a sample file so that you can follow along. I hope this is what you're looking for...

  1. 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
  2. Create a data validation table as a list from each of the headers.
  3. 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.

@Justin_Blush 

 

Could you share your updated workbook so that I can search for the issue? (please remove any sensitive information)

 

@PReagan 

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.

@Justin_Blush 

 

  1. Add an additional data validation list with the headings of each "Description" column.
  2. Changing each "Description" column to it's own table will allow the list to be dynamic (like done with each U.S. Policy column above
  3. You will have to change the formula and "applies to" section for each column.

Change "Description" column to:

    • =AND($A$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),A14)))>0)

       

    • Applies to $A$14:$A$17

Change "Description2" column to:

    • =AND($B$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),B14)))>0)
    • Applies to $B$14:$B$17
    • Change "Description3" column to:
    • =AND($C$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),C14)))>0)
    • Applies to $C$14:$C$17

Review the following workbook and let me know if you have any other questions or concerns.

Thank you, sorry for the delay in response. This is close but is missing a layer of information.

My data validation table would not have the descriptions but rather partner nations i.e. U.K., Mexico, and Canada. By selecting a partner nation it would focus the search/highlighting to description columns that are pertinent to the respective nation.

For example if I was to select the U.K. the highlighting would be focused to description columns 1,3, and 5. Whereas in the case of Mexico it would focus on 1,2, and 3 — and Canada would be description columns 1,3, and 4.

Each partner nation would have overlapping description columns hence 1 and 3 but each would have columns that were isolated to the respective nations.

Hopefully this makes sense. I tried to figure it out on my own but keep running into a wall. Thank you again!!!
best response confirmed by Justin_Blush (Copper Contributor)
Solution

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.

@PReagan Here you are sir.  thank you for the help

@Justin_Blush

 

I am not understanding which columns should be referenced for each partner nation. Should it be:

  1. Only the columns with the header of the partner nation?
  2. Columns with the header of the partner nation AND columns that contain the partner nation's name?
  3. Only the columns that contain the partner nation's name?

In general, what you can do is apply a separate conditional format for each section of columns based on the partner nation's name as the condition. For example:

 

Focus of Exercise "TSC", Partner Nation "Mexico"

Create a conditional format with the formula:

=AND($D$4="Mexico", SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($B$4)<>"",INDIRECT($B$4)),N9)))>0)

 Apply this conditional format to all columns that apply. I chose the "applies to" range $Q$9:$R$143,$N$9:$N$143

 

For each partner nation, you would simply change "Mexico" in the formula to the partner nations name and change the "applies to" range to the relevant columns that you want to search in.

 

Please let me know if you have any other questions.

@PReagan 

 

Hello!  Sorry for the delay.  Due to what has been going on in the world it has been a bit hectic lately.  Your last correspondence worked perfectly!

 

I have another question.  Now that the table highlights information properly taking into account both the categories and now the partner nations.  The last time I sent you the worksheet there were two sheets: Sheet 1 had the categories and partner nations in tables with respective keywords; Sheet 2 had the quotes and text from government documents that were being highlighted based upon selection.

 

I am wondering if I can have another sheet (sheet 3) that would basically copy and paste only the highlighted cells from Sheet 2 and omit all cells from sheet 2 that were not highlighted.  So the goal is to further isolate the information onto a sheet and omit all unnecessary information.  Thank you and let me know if I need to explain further.

@PReagan 

 

One more thing.  On the third sheet the information will be transposed from the columns on sheet 2 into rows on sheet 3.

1 best response

Accepted Solutions
best response confirmed by Justin_Blush (Copper Contributor)
Solution

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.

View solution in original post