Reference a conditional formatted symbol in a forumla

Hello,

I have a scoring system set up that puts the score through conditional formatting to generate an icon set. i.e traffic lights (specifically: "3 traffic lights (unrimmed)". What I would like to know is can I create a formula that checks the symbol that have been placed in the cell.

For example: If symbol is green or yellow do nothing. If symbol is red copy cellXX to sheetXX.

This is for a performance dashboard. What I want to create is a separate sheet that is populated with a list of the performance areas which scored low enough to get a red traffic light symbol.

The only part I cannot figure out is how to reference the symbol in the formula. Any help is greatly appreciated.

I cannot attach the sheet as it contains private personal information.

Hello and welcome to the forum.

We can't tell you exactly how to do something without seeing a small representative sample of the data along with the desired result (which can be entered manually).

That being said, tasks like this are completed by building a formula that mimics the conditional formatting that would make a cell show as a red traffic light.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

I have attached a simplified example with notes describing what I am trying to achieve.

I noticed that you have different Conditional Formatting going down column E.

Assuming that this was a mistake and you want to pull all criteria with a value < 2.5 over with a formula, try this in A12:

=IFERROR(INDEX(A:A,SMALL(IF(E$2:E$7<2.5,ROW(E$2:E$7)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula down as far as needed.

I have used the formula you suggested and it works. It doesn't use the conditional formatting icon in the formula but it still achieves the same result by using the underlying number that drives the conditional formatting in the first place. So perhaps my approach was wrong from the get go.

However, you have answer my question. Thank you very much for your help.

My next challenge is to figure how to make this generate that list on a new sheet with various headings surrounding it!

Originally Posted by CallumRussell

It doesn't use the conditional formatting icon in the formula but it still achieves the same result by using the underlying number that drives the conditional formatting in the first place.

Exactly. Formulas can't see Conditional Formatting so you have to mimic what is making the Conditional Formatting be applied, as explained in post #2.

However, you have answer my question. Thank you very much for your help.

Happy to help. Thanks for the rep!

My next challenge is to figure how to make this generate that list on a new sheet with various headings surrounding it!

You can put the formula from post #4 anywhere. You will just have to include the sheet name reference before "A:A" and both instances of "E$2:E$7".