Forum Discussion
CoreyWFG
Jun 05, 2023Copper Contributor
Bring cell colour with the result
I have a sheet with 4 tabs of data, I have set up a 5th tab to compare the first 4 tabs and take the lowest value and post it in the corresponding cell in tab 5 (which I already have completed) but t...
CoreyWFG
Jun 05, 2023Copper Contributor
I should mention that there can and will be some cells that have an equal value, so would there be a way to differentiate those as well? Like a result with 2 matching numbers fill with one colour and text with he other?
- mtarlerJun 07, 2023Silver Contributor
alternatively you report that info in an adjacent cell or even in the same cell with the value. for example
= LET(vals, TOCOL( SHEET1:SHEET4!A1),
companies, {"abc"; "def"; "ghi"; "jkl"},
TEXTJOIN(" - ",, FILTER( companies, vals=MIN(vals) ), MIN(vals)))
I would further recommend making "companies" a Named variable or range and use short abbreviations like 1 or 2 letters for each company.
Lastly you can then apply conditional formatting on the sheet/range to highlight cells however you like based on those characters. I would first check if there is more than 1 "-" (i.e. a tie for min) and if not then if LEFT( ,2)=... then color... and if >1 "-" then some other color (you will need 1 rule for each color/condition)I added a tab to the above example to show this alternative: