Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display a graphic based on a cell value per row

    I have a spreadsheet that lists all rivers in the area and any one time we wnt to know the Flood warning level about all rivers. Each flood warning has its own icon. We would like to use these icons to be displayed in the sheet.

    eg.



    Is this possible? Most stuff i've see only seems to hide pictures until the info in a cell is entered but I would want them all displayed.

    Thanks



  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    183
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Specifically, what "icons" are you trying to use? Can you post a manually generated example???

  4. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    They are specific flood warning symbols which unfortuantley I don't have (as usual I have been asked by a 3rd party with very little info) but they are jpegs. Apologies that the table I created above didn't work.


    I suggested basic condititional formatting would help them flag the information but thats not enough, they want the pretty symbols by each river

    rivers.png
    Last edited by meames; 2012-08-29 at 03:19. Reason: problem with table

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    Does the attached do what you want?

    The SYMBOLS sheet has pics and each are located in a cell which is a named range ("low", "high" severe")

    The other sheet has the names of the rivers in Col A. In Col B, data validation is used to choose one of the three warnings (from the list in SYMBOLS). The graphics in column C are each linked to a named formula which is named for the river in Col A. The named formulas each use INDIRECT to refer to the warning in Col B. So when the text in col B is changed, the graphic in Col C is changed as well.

    Steve
    Attached Files Attached Files

  6. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think so, thanks.

    I can see what you have done but not how you have done it. Thanks.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    It is done with range names. I thought my descriptions were relatively complete. Do you need clarification on anything in particular?

    Steve

  8. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is great - is it possible to display images that are separate .jpg files? I want to create a file with photos of students interviewing with different companies. There are several hundred students and it would be easier (and have a much smaller file) if the final spreadsheet did not contains all the photos.

    Thanks.

    Larry

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    You can link photos but they will display as hyperlinks. When pressed the hyperlinks will open the default program for viewing the file, it will not display in excel. If you want them to display in Excel, I think you will have to embed them. You could shrink the photos to close to the display size and i think that will make the excel file smaller.

    Steve

  10. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, Netherlands
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    It is done with range names . . . need clarification?
    Steve,
    Been looking at your solution. Am impressed. I think I understand some of your explanation. but where do you decide which pic to display?
    Conditional formating, or so?

    Thanks
    Leon

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    It is not conditional formatting. It is named formulas (aka named ranges).

    Look at sheet 1 for example. You want to change the warning level for the Thames (row 2). To change the level, you go to B2 and enter a value (type or from the datavalidation list). For this example we will enter "high".

    If you select the picture placed in C2 you will see that the formula bar indicates "=Thames". "Thames" is a named formula.

    If you go to Formulas - Name manager and select the name "Thames" you will see it refers to:
    =INDIRECT(Sheet1!$B$2)

    This is an indirect reference to whatever is in Sheet1B2. In that cell we just entered in "high". Therefore the reference in the named formula "Thames" is =Indirect("high"). So we need to know now, what is the named formula "high"

    If you go to Formulas - Name manager and select the name "high" you will see it refers to:
    =Symbols!$B$3

    So what is displayed in the graphic is whatever is in the Symbols sheet in Cell B3, which is the graphic for "high" warning.

    The setup for this type of work requires a named formula for each of the warning levels (low, high, severe) each one referring to the range (which in this example is only 1 cell) that contains the appropriate graphic. Each river graphic also needs a named formula (I named them for the river, the name does not matter) which refers to a formula using INDIRECT which points to the location of where you enter the warning level (in these examples col B of the same row). If you add more rivers you will need to add more named formulas and more graphic and refer to the names.

    Better?

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •