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.
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!!!
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.
One more thing. On the third sheet the information will be transposed from the columns on sheet 2 into rows on sheet 3.
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.
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.
PReagan Here you are sir. thank you for the help