Conditional formatting- insert a picture when condition is true

[pboise]

Greetings, We have an excel spreadsheet that tracks a persons training status using a picture of 1/4 filled black circle, 1/2 filled black circle,3/4 and 4/4 filled black circle.the picture is added when a person meets the training standard. We would like to use "Conditional Formatting" to insert the picture when we enter a number.

if there is a "bigger, better, faster method, we are open to suggestions but we are mandated to use this spreadsheet.


your thoughts, please and thank you

 

[Luke M]

Can't do that directly using conditional formatting, but you can do it with a bit of formula magic. First, we need to set some things up. Guessing at your training status relation to picture, make a 2x5 table. First column contains the lower boundary for each image, something like this (I'll assume its in cells A2:B6 of Sheet2.


0 <no picture>

25% <1/4 circle>

50% <1/2 circle>

75% <3/4 circle>

100% <fill circle>


For the right hand column, you'll want to align the picture within the cell as desired (hold Alt to align to cell border)


Next, we need to define a named range. (Assumption) your employee status cells is in F2. Call the named range "MyPic" with a formula of:

=LOOKUP($F2,Sheet2!$A$2:$B$6)

Now, in the cell next to emplyee status (say, G2), draw a rectangle/square. Select the square, and in the formula bar, type:

=MyPic


Voila! You've now created a picture link, which will dyamically change as the employee status changes.

 

[pboise]

Luke, Thank You. I will set this up this this afternnon