# Concatenating cell value to include C.F. and GREATER/LESS THAN in relation to cell color

#### DarrenK

##### Board Regular
Hello,

Not certain this can be done but wanted to reach out before I wrote it off.

I have 5 cells (C4,C6,C8,C10,C12) that have percentages. Those are conditionally formatted with the Green-Yellow-Red Color Scale. If there are 5 different values in the cells, it displays the following colors:

In the adjacent cell to the left (B4,B6,B8,B10,B12), there are department names listed. For ease of understanding for anyone else viewing the file, I used the concatenate formula in the adjacent cells to the right (Column D):
=CONCAT("iF "&B4& "department gets a call, it will be answered with the " &(random cell)& " priority.")

In the random cell, I was trying to get the colors of the cells to work along with IF statements so that whichever of those 5 cells has the highest percentage, it adds which priority will be assigned into the concatenated formula. (Dark green would be HIGHEST, Red would be LOWEST. With the other 3 colors somewhere in between on the spectrum (SECOND HIGHEST, MEDIUM, SECOND LOWEST)) .

I may be overcomplicating it, but is there a way to utilize C.F. color along with IF statements to help determine which of the cells would have the highest value all the way to the lowest value? Or would it be easier to just use multiple IF statements to compare all 5 cells' values and come to the same result? And then with those results, determine which of 5 priority values would be populated in the concatenated statement? I'm really bad with embedded IF statements and would need help.

(EX: If dept 1 on top had the lowest of the 5 percentages, it's corresponding D cell would have the formula along with LOWEST priority. But then if 2 departments had the same percentage, their D formula would display the same value)

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### StephenCrump

##### MrExcel MVP
Not totally clear, but heading in the right direction perhaps?

Cell Formulas
RangeFormula
E4,E12,E10,E8,E6E4="Answer with " & INDEX(Priorities,RANK.EQ(C4,C\$4:C\$12)) & " priority"
Named Ranges
NameRefers ToCells
Priorities=Sheet1!\$A\$2:\$A\$6E12, E10, E8, E6, E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:C12Other TypeColor scaleNO

At the moment, we have 5 scores and 5 priorities, but if you like, this could easily be generalised for n scores, m priorities, n<>m.

#### DarrenK

##### Board Regular
Stephen you're amazing! That's exactly what I need it to do. Thank you a great deal!!!

And it eliminates the need for a VBA code which I was trying really hard to steer clear of.

#### StephenCrump

##### MrExcel MVP
You're welcome. I'm glad we could help.

Replies
8
Views
317
Replies
9
Views
131
Replies
11
Views
663
Replies
5
Views
203
Replies
3
Views
306

1,147,475
Messages
5,741,344
Members
423,656
Latest member
Medrok2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back