Mar 03 2020 11:01 AM
Mar 03 2020 12:12 PM
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.
Mar 04 2020 07:56 AM
Mar 04 2020 02:41 PM
Attached is a sample file so that you can follow along. I hope this is what you're looking for...
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),A14)))>0
If you have any question, please do not hesitate to ask.
Mar 06 2020 06:38 AM
Mar 06 2020 07:11 AM - edited Mar 06 2020 07:11 AM
Could you share your updated workbook so that I can search for the issue? (please remove any sensitive information)
Mar 06 2020 11:52 AM
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.
Mar 08 2020 07:33 AM
Change "Description" column to:
=AND($A$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),A14)))>0)
Change "Description2" column to:
=AND($B$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),B14)))>0)
=AND($C$13=$B$11,SUMPRODUCT(--ISNUMBER(SEARCH(IF(INDIRECT($A$11)<>"",INDIRECT($A$11)),C14)))>0)
Review the following workbook and let me know if you have any other questions or concerns.
Mar 17 2020 03:13 PM
Mar 18 2020 06:31 AM
SolutionHello @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.
Mar 19 2020 01:19 PM
@PReagan Here you are sir. thank you for the help
Mar 20 2020 06:27 AM
I am not understanding which columns should be referenced for each partner nation. Should it be:
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.
Apr 06 2020 02:21 PM
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.
Apr 06 2020 03:15 PM
One more thing. On the third sheet the information will be transposed from the columns on sheet 2 into rows on sheet 3.
Mar 18 2020 06:31 AM
SolutionHello @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.