Forum Discussion
Drop down list - highlights cells
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.
I am not understanding which columns should be referenced for each partner nation. Should it be:
- Only the columns with the header of the partner nation?
- Columns with the header of the partner nation AND columns that contain the partner nation's name?
- 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.
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.