Conditional Colour Formatting for cells in a coloum depending on match in another sheet
Hello,
I am looking for advice on how to conditionally colour format multiple cells in a colomn depending on if they match with entries in another sheet.
I have two sheets where tags have been given to different papers. Each is assigned a colour (blue and green).
Sheet 1 (colour assigend: blue)
TitelTagsPaper 1Irrigation, water, agriculturePaper 2Precipitation, flood
Sheet 2 (colour assigned: green)
TitleTagsPaper 3Groundwater, agriculturePaper 4Precipitation, agriculture, drought
In a third sheet, I want to
(1) Combine the entries in both sheets, which I did with the VSTACK formula;
(2) Filter through the results according to the tags, which I did with the ISNUMBER and SEARCH formula;
(3) Have the title row in the resulting entries highlighted with fill colour, depending on the origin sheets’ colour (i.e., if entry(ies)( from sheet 1 = title cell(s) blue; entry(ies) from sheet 2 = title cell(s) green).
For (3), I tried to use Conditional Formatting -> Use formula … with different formulas, but do not manage to achieve what I am looking for. Either only one or all cells are coloured.
Has anyone a suggestion how to achieve (3), i.e., what formula to be used in conditional formating?
The result should look like this (example with agriculture as tag search term):
Search tag:agricultureTitleTagsPaper 1Irrigation, water, agriculturePaper 3Groundwater, agriculturePaper 4Precipitation, agriculture, drought
(where font colour is a substitute for fill color since fill colour seems not be supported in tables here).
Thanks in advance!
Paul
Hello, I am looking for advice on how to conditionally colour format multiple cells in a colomn depending on if they match with entries in another sheet. I have two sheets where tags have been given to different papers. Each is assigned a colour (blue and green).Sheet 1 (colour assigend: blue)TitelTagsPaper 1Irrigation, water, agriculturePaper 2Precipitation, flood Sheet 2 (colour assigned: green)TitleTagsPaper 3Groundwater, agriculturePaper 4Precipitation, agriculture, drought In a third sheet, I want to(1) Combine the entries in both sheets, which I did with the VSTACK formula;(2) Filter through the results according to the tags, which I did with the ISNUMBER and SEARCH formula;(3) Have the title row in the resulting entries highlighted with fill colour, depending on the origin sheets’ colour (i.e., if entry(ies)( from sheet 1 = title cell(s) blue; entry(ies) from sheet 2 = title cell(s) green). For (3), I tried to use Conditional Formatting -> Use formula … with different formulas, but do not manage to achieve what I am looking for. Either only one or all cells are coloured. Has anyone a suggestion how to achieve (3), i.e., what formula to be used in conditional formating? The result should look like this (example with agriculture as tag search term): Search tag:agricultureTitleTagsPaper 1Irrigation, water, agriculturePaper 3Groundwater, agriculturePaper 4Precipitation, agriculture, drought(where font colour is a substitute for fill color since fill colour seems not be supported in tables here). Thanks in advance!Paul Read More