Forum Discussion

Justin_Blush's avatar
Justin_Blush
Copper Contributor
Mar 03, 2020
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...
excel
Formulas and Functions
  • PReagan's avatar
    PReagan
    Mar 18, 2020

    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's avatar
PReagan
Bronze Contributor
Mar 04, 2020

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.

Justin_Blush's avatar
Justin_Blush
Copper Contributor
Mar 06, 2020
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.
  • PReagan's avatar
    PReagan
    Bronze Contributor
    Mar 06, 2020

    Justin_Blush 

     

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

     

    • Justin_Blush's avatar
      Justin_Blush
      Copper Contributor
      Mar 06, 2020

      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.

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Mar 08, 2020

        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.

Resources