URGENT! Please Help (: Formula to add image according to data input

[candycedd]

First of all, I am fairly new to excel and I am working on a project for a friend and would kindly like your help or input. This is urgent, so I would appreciate any suggestions Also, apologies in advance, due to my lack of excel experience, I'm not too familiar with excel lingo. So please be patient & I will try my best to clarify what I need help with....

I'm using excel 2007, pc

A friend asked me to create a speedometer for her company so that she can manipulate the number (let's call it "score") herself. Miraculously I was able to do this but here's what I have been struggling with. For the whatever the number of the "score" is, she wants an image along with it. The score is based from a 0% - 100% scaling system. So it goes as follows:

0%-49% = unsatisfactory
50%-79% = satisfactory
80%-100% = excellent

Now if the score falls within the unsatisfactory, she wants a sad face image to appear. If the score falls within the satisfactory, she wants a neutral smiley face to appear. If the score falls within the excellent, she wants a happy face image to appear. Are you following?

Now I have found a few tutorials on how to make an image appear according to a chosen data cell, but I haven't figured out how to make a formula more specific to what I need. What is the appropriate formula to do this?

I really hope someone knows and I hope even more I'm specific enough....

 

[RJPotts]

First of all, I am fairly new to excel and I am working on a project for a friend and would kindly like your help or input. This is urgent, so I would appreciate any suggestions Also, apologies in advance, due to my lack of excel experience, I'm not too familiar with excel lingo. So please be patient & I will try my best to clarify what I need help with....

I'm using excel 2007, pc

A friend asked me to create a speedometer for her company so that she can manipulate the number (let's call it "score") herself. Miraculously I was able to do this but here's what I have been struggling with. For the whatever the number of the "score" is, she wants an image along with it. The score is based from a 0% - 100% scaling system. So it goes as follows:

0%-49% = unsatisfactory
50%-79% = satisfactory
80%-100% = excellent

Now if the score falls within the unsatisfactory, she wants a sad face image to appear. If the score falls within the satisfactory, she wants a neutral smiley face to appear. If the score falls within the excellent, she wants a happy face image to appear. Are you following?

Now I have found a few tutorials on how to make an image appear according to a chosen data cell, but I haven't figured out how to make a formula more specific to what I need. What is the appropriate formula to do this?

I really hope someone knows and I hope even more I'm specific enough....

Lets assume the score (in percent) is in cell B1 and you would like the image to appear in D1.

Create another tab, lets call it PicTable, on that tab add the following;

On the first tab (i.e. where you want the image to appear) type, in cell D1;

=VLOOKUP(B1,PicTable!A1:D3,4)

So far, depending on the percentage in cell B1 you should have 'Sad', 'Neutral', or 'Smile' appearing in cell D1 yes?

Find 3 suitable images, insert them anywhere on your first tab (i.e. the one where the final image is to appear).

Name the images by selecting each one and then in the name box (the one to the left of the formula bar) typing Sad, Neutral, Smile, as appropriate.

Right-click on the Tab name (i.e. near the bottom of the screen) & then in the white area paste the following;

Private Sub Worksheet_Calculate()

        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("D1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
End Sub

Close VBA, return to you first tab and try changing the percentage.

Best regards
Richard