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

candycedd

New Member
Joined
Jun 22, 2012
Messages
1
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

Board Regular
Joined
Apr 11, 2007
Messages
139
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;

Code:
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
 

Forum statistics

Threads
1,081,518
Messages
5,359,244
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top