Solved: Change/Insert autoshape based on cell value

[Lawrence]

I have several sheets that will measure performance. Column M will return a green/yellow/red light based on the value in column I and the parameters in columns B & C. Column L will return an arrow based on the light color, or parameter, whichever one is easier.

I found a way to do the lights using a bubble graph driven by some formulas on Sheet1. This works, but it is time consuming to setup as I will have close to 100 rows on 3 different sheets. Also, I don?t know how to deal with the arrows.

So, is there a simpler way of doing this? Can the cell be linked to a picture (or shape) and return the appropriate shape based on value?

[lucas]

You might try using fonts as in the example attached.

Example of how it works:
on row 2 the correct or incorrect answer in put into cell E2.
Cell F2 has a formula that checks for the correct answer in cell E2 and responds by putting a j in F2 if it is correct or an L in cell F2 if it is incorrect.

The user has set the fonts to windings to get the smily faces and frowns.

change the font on the cell F2 to see the actual letters....use a different font to get the effect you desire..

There is also one that uses flash for correct and incorrect responses.

Hope this helps.

[Lawrence]

Thanks Steve, this is along the lines of what I am looking for and much easier than what I was trying to do. But I have a few questions on how this works:

1) I see how the formula works in F2, but how does it change color?

2) I noticed that E2 has a name range VB_Trigger01. Does it have anything to do with the above changing color? If so, how?

3) In my case my formula in F2 would reference the same letter and the symbol would change color according to the value in E2. It would be something along the lines of =IF(E2="","n",IF(E2=4,"n","n")) with the font set to "webdings" (marlett "n" and wingdings "l" also work). Is there an easy way to do it?

Thanks for your help.

[Bob Phillips]

1) I see how the formula works in F2, but how does it change color?

Conditional Formatting - Format>Conditional Formatting



2) I noticed that E2 has a name range VB_Trigger01. Does it have anything to do with the above changing color? If so, how?

Doesn't look like it.


3) In my case my formula in F2 would reference the same letter and it would change color according to the value in E2. It would be something along the lines of =IF(E2="","n",IF(E2=4,"n","n")) with the font set to "webdings" (marlett "n" and wingdings "l" also work). Is there an easy way to do it?

As well as changing the formula, although having the same value for whatever is input seems a bit pointless, you would need to change the values being tested for in the conditional formatting.

[Lawrence]

Conditional Formatting - Format>Conditional Formatting.
Dummy me, I didn't even check for that as I thought it was some fancy schmancy stuff. :bug:


although having the same value for whatever is input seems a bit pointless, you would need to change the values being tested for in the conditional formatting.

In my case I would put the formula in the conditional formatting and cell F2 would simply contain the letter "n".

Thanks to Steve for posting that example, it is exactly what I was looking for.

Thanks to Bob for reminding me that simplicity rules!

[lucas]

The named ranges are a leftover from when I was helping someone get a flash presentation on the correct or incorrect answer and they can be removed for your purpose.

As Bob points out, you just need to put the formula in column F and set the conditional formatting for that row. Click on one of the cells in the example and go to conditional formatting to see that just the letters are used...no formula.

[lucas]

My last post may have mislead you a little. There will be no other formula needed for the conditional formatting except the

="J"

or

="L" in the example.

You can select the entire row and apply this formatting if needed.