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.

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.

      • Justin_Blush's avatar
        Justin_Blush
        Copper Contributor
        Mar 17, 2020
        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!!!

Resources