Auto posting image based on cell vaue

[Drasston369]

Hey Team,

So I've got a target spreadsheet for sales. And I would like to have an image appear based on the value of the cell.
So for instance
A1 will be there target
A2 will be there actual sales
A3 will be an image based on their result.

With the image being a bronze circle if they achieve up to 89% of target, silver circle if the achieve 90-99%, Gold Star if the achieve 100-109% and a silver star if they achieve 110%+.

Ideally once the actual sales have been inputted, the appropriate image will appear in A3.

Is there a way to do this?

Thanks heaps, I'm still trying to figure my way around this.

Clyde

 

[My Aswer Is This]

I would suggest seeing if conditional formatting will do what you want. They have a set of icons you can choose from.

 

[Drasston369]

I would suggest seeing if conditional formatting will do what you want. They have a set of icons you can choose from.

I tried that but could only get it to work for a single cell. Can it be formatted for what's input in a different cell? And can you customize the icon? Or do you have to use the stock ones.

Thanks!

 

[My Aswer Is This]

Well I never like saying no because someone else here may say yes. I guess nearly anything can be done with Excel if you write enough code. But I know of no way to modify the icons.

So you would have to have three images on your workbook and give them some name and then we could write the code you want. The image would then have to be sized to fit into the cell. Have you tried using the shape tool and making you a shape that would work? You can make a shape of nearly any shape color it and then the shape with code could be sized to fit the cell.

If you already have the three images in your workbook give me the name of those three images.

 

[Drasston369]

Yeah I do, ok so it's Bronz, Silv, GoldS, Plat.

 

[My Aswer Is This]

This is possible yes.
Are you willing to use Vba?

Please write out the formulas please.

Your first example said up to 89% of target. So if a person had zero he would get "Broze"

 

[Drasston369]

With the image being a bronze circle if they achieve up to 89% of target, silver circle if the achieve 90-99%, Gold Star if the achieve 100-109% and a silver star if they achieve 110%+.
89% or lower would get "Bronz"
90-99% would get "Silv"
100-109% would get "GoldS"
110%+ would get "Plat"
Is that what you mean? VBA is fine as long as it works haha.

 

[Drasston369]

Ok, would be easier/possible to base it on a shape? by changing the fill/gradient

For instance saying something like if the value in A2 is greater than A1 by 10% then the shape turns silver?
And if it's less, then it turns silver but if its less by more than 10% less it turns bronze?

Thanks

 

[tonyyy]

Clyde,

Welcome to the Board.

A1 will be there target
A2 will be there actual sales
A3 will be an image based on their result.

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, shp As Shape, Performance As Double, fPath As String
fPath = "C:\Docs 2017\2017 Gigs\MrExcel\" 'Change to your directory path and include "\" at the end
Set r = Range("A3")
If Not Intersect(Range("A2"), Target) Is Nothing Then
    For Each shp In ActiveSheet.Shapes
        If shp.Left = r.Left And shp.Top = r.Top Then shp.Delete
    Next shp
    
    Performance = r.Offset(-1, 0).Value / r.Offset(-2, 0)
    Select Case Performance
        Case Is < 0.9
            Set shp = ActiveSheet.Shapes.AddPicture(Filename:=fPath & "Bronze.jpg", linktofile:=msoFalse, _
                savewithdocument:=msoTrue, Left:=r.Left, Top:=r.Top, _
                Width:=-1, Height:=-1)
        Case Is < 1
            Set shp = ActiveSheet.Shapes.AddPicture(Filename:=fPath & "Silver.jpg", linktofile:=msoFalse, _
                savewithdocument:=msoTrue, Left:=r.Left, Top:=r.Top, _
                Width:=-1, Height:=-1)
        Case Is < 1.1
            Set shp = ActiveSheet.Shapes.AddPicture(Filename:=fPath & "Gold.jpg", linktofile:=msoFalse, _
                savewithdocument:=msoTrue, Left:=r.Left, Top:=r.Top, _
                Width:=-1, Height:=-1)
        Case Is > 1.09
            Set shp = ActiveSheet.Shapes.AddPicture(Filename:=fPath & "Platinum.jpg", linktofile:=msoFalse, _
                savewithdocument:=msoTrue, Left:=r.Left, Top:=r.Top, _
                Width:=-1, Height:=-1)
    End Select
    With shp
        .LockAspectRatio = msoTrue
        .Width = Columns("A").Width
        Rows(r.Row).RowHeight = .Height
    End With
End If
End Sub

Notes:
The code should be pasted into the sheet module that corresponds to the sheet with the target and actual sales data.
Change the directory path to match the folder that contains the .jpg's.
Change the .jpg names to match your .jpg names.

Cheers,

tonyyy

 

[Drasston369]

I like that idea, but I should have mentioned I need to be able to duplicate the workbook on multiple computers.
Sorry about that! Looks cool on my computer though!