Formulas results that show Images

[burt0na]

Hi guys,

I am looking to produce a formula whereby depending on the result an image is inserted.

For Example: I have 3 images I want to represent good, bad and indifferent, good would be anything above 70%, indifferent 30%-70% and bad below 30% but rather than the formula showing the figure id like a image to show.

I dont know if this is possible with a formula, it maybe a piece of code but its Excel so im sure it is

Any help would be appreciated

Andy

 

[Jon von der Heyden]

Hi Andy

What you are asking for really isn't very straight forward. What are the images if I may ask? If it is like a simple traffic light system then consider using a symbol font and return pictures using the relevant character. And conditional formatting can be used for the colour.

If you are determined to go down the picture root and you want this done witha formula then you would need to create a UDF (user-defined function) and in that function use a windows timer to call a method that inserts the image over the calling cell.

You can read up on Windows timers here: http://www.cpearson.com/excel/OnTime.aspx

And Colin Legg has written an article on how to use the timer in a UDF here: http://colinlegg.co.uk/Excel/ListDistinctOrUnique.html

 

[burt0na]

Hi Jon,

Thanks for the reply.

They are 3 specific images my boss wants to use. It is basically a traffic light system and I did suggest doing something simpler but the images he has found are the ones he'd like to use so thought I would explore this for him.

Thanks for the links I will take a look

Any other info would be great

Andy

 

[Jon von der Heyden]

Seems like I spoke too soon. Much easier than I thought!

http://www.mrexcel.com/forum/showthread.php?t=493132

 

[burt0na]

Nice one Jon.

Im not really a master of VBA so that doesnt mean much to me but will give it a go

Any further info/advise you can give me in terms of getting the criteria in place for relevant picture to show based on the 3 conditions mentioned above would be great.

Andy

 

[Jon von der Heyden]

Ok so here's a bit more info on how to get this working.

Copy Zach's code to a module in your workbook:

Function INSERTPIC(sFullName As String) As Variant

    Dim rng1 As Range, rCall As Range
    Dim oShp As Object, sFName As String
    Dim dblLeft As Double, dblRight As Double
    Dim dblHeight As Double, dblWidth As Double
    Dim dblTop As Double, dblBottom As Double
    
    If Dir(sFullName, vbNormal) = "" Then
        INSERTPIC = "Bad file path/name"
        Exit Function
    End If
    sFName = Right(sFullName, Len(sFullName) - InStrRev(sFullName, "\"))
    Set rCall = Application.Caller
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each oShp In ActiveSheet.Shapes
        Set rng1 = Range(oShp.TopLeftCell, oShp.BottomRightCell)
        If Not Intersect(rng1, rCall) Is Nothing Then
            oShp.Delete
        End If
    Next
    
    Set oShp = rCall.Parent.Pictures.Insert(sFullName)
    dblTop = rCall.Top
    dblLeft = rCall.Left
    dblBottom = rCall.Offset(1, 0).Top
    dblRight = rCall.Offset(0, 1).Left
    dblWidth = dblRight - dblLeft
    dblHeight = dblBottom - dblTop
    
    oShp.Top = dblTop
    oShp.Left = dblLeft
    oShp.Width = dblWidth
    oShp.Height = dblHeight
    
    INSERTPIC = sFName
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Function

Next create a new sheet called LookupPic. It should look something like:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #538DD5;;">Category Value</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #538DD5;;">Picture Path</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.00%</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C:/Pics/Pic1.jpg</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30.00%</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C:/Pics/Pic2.jpg</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">70.00%</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C:/Pics/Pic3.jpg</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">LookupPic</p><br /><br />

Next go to the sheet that you want the results to be displayed. Let's say that you are checking cell A1 and you want the pic to be displayed in B1:

In B1:
=INSERTPIC(VLOOKUP(A1,LookupPic!$A$2:$B$4,2,1))

 

[Andrew Poulsom]

Here's another way:

http://www.youtube.com/watch?v=Ww23IbylOuY

 

[burt0na]

Hi,

Thanks for the the detailed approach Jon.

I have put the code into a module, created the table and renamed the sheet accordingly, put the pictures in a folder and amended the formula tyo look for that path and tried to run the formula from the cells as stated but am getting this message #VALUE!

I am running this from Excel 2003 would that make any difference?

Andy

 

[Andrew Poulsom]

Did you look at the podcast in the link I posted? No VBA required.

 

[Jon von der Heyden]

Andrew, that is one cool trick!

Andy, seriously worth watching that youtube video.

But if you are going to stick with the VBA method, what result does the VLOOKUP formula return (i.e. leave off the INSERTPIC for now)?