+ Reply to Thread
Results 1 to 20 of 20

Display Picture "A" or Picture "B" based on value in Cell A1

  1. #1
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Display Picture "A" or Picture "B" based on value in Cell A1

    Problem: Based on value "A" or "B" being in Cell A1; display Picture A or Picture B.


    If it makes it easier, we can always display Picture A (default) and only switch it to Picture B when value B is in A1.

    These are two simple similar pictures (or objects?) used for instructional purposes on a spreadsheet where test results are recorded. I made the pictures in powerpoint; basically a grouped collection of a couple little text boxes and a couple arrows. The two picture are really only to address a clockwise or counterclockwise situation. I saved the pictures as png. The page will be printed and the picture represents about 1/4 of the printed page.

    Thanks for any guidance - lets avoid programming if possible. All the examples I have seen are very involved.

    j

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    For an alternative non-VBA solution, try this:

    Assumption: Pictures are stored on Sheet2 and will be dynamically shown on Sheet1.

    Select Sheet2 and turn off Grid Lines
    View...Uncheck: Grid Lines

    1) For each picture to be displayed:
    1a. Insert.Picture...(select picture and put it in the sheet).
    1b. Select the range of cells that is under the picture.
    1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text:
    Example for a picture of an Elephant:
    Formulas.Name_Manager.New
    ...Name: picElephant
    ...Refers to: (this is whatever range you've selected under the elephant pic)

    2) Build your data validation list on a cell in Sheet1 and pick one of the items.

    3) Create a dynamic range name that refers to that cell:
    Formulas.Name_Manager.New
    ...Name: ShowMyPic
    ...RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
    or whatever cell you chose tha contains the dropdown list.

    4) Copy the range of cells under one of the pictures from Sheet2
    5) Select the display cell on Sheet1.
    6) Home.Paste.Paste.As_Picture.Picture_Link
    7) With the picture selected, type this in the formula bar, then press [Enter]:
    =ShowMyPic

    The picture will be replaced by the picture referred to by the dropdown list.

    Each time you select a different item in the list, the associated picture will appear in the picture box and resize appropriately

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Thanks for getting back. I can work on it now.

    The pictures must be in the file same file - there is only one file.

    Maybe put on a different tab, perhaps that is what you mean by sheet.

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Yes...sheet = tab.

    How's the weather in Boston? Oh, wait, I can just look out the window and see for myself.

  5. #5
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Phase one complete. Not sure on 2) build data validation on a cell in sheet 1 (sheet one where I'm going to use the pics?)

    Now that I have a better understanding of this apporach not sure will work. The user can not be bothered with picking the instructional pic. Needs to happed automatically when a required value is entered in Cell A1.


    Regards,

    J

  6. #6
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Various users will be entering test data results into a 5 sheet excel file (2010.) The file is completely locked down so the user can only enter test results. Numerous messages will pop-up based on the users questionable or missing data.

    Concerning the need to display Picture A or B; a formula can deduce which picture is more instructional based on the file name. Files are named by the serial number which indicates the product type, hence which picture is best.

    --j

  7. #7
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Since we didn't have a sample model to work with, I posted a generic approach.
    Instead of a data validation cell, the display area can be driven by a formula that determines which image appears.
    With more details, we may be able to provide a more custom solution.

  8. #8
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Need more info Mr. Coderre?

  9. #9
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    In the solution I offered, this: =INDIRECT("pic"&Sheet1!$A$1)
    creates the reference to the image to be displayed.

    You'd need to change that formula to something specific to your situation.
    For instance, if you were testing responses, something like this might work:
    Please Login or Register  to view this content.
    In that example, you'd need to define range names picCorrectAnswerImage and picWrongAnswerImage.

  10. #10
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Hi Ron-

    I'm still trying this. I created the picCorrectAnswerImage, picCorrectAnswerImage and ShowMyPic with the INDIRECT ref to the cell where the "A" or "B" entry is made. After that gets little fuzzy.

  11. #11
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    One of my tabs has "&" in the name - is that a problem? ie Instructions & Summary [tab name]

  12. #12
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    No, odd characters shouldn't be a problem because the tab name in the formula is enclosed in single-quotes.
    Can you post a small sample file? That'll help us spot the issue.

  13. #13
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    PM for Ron

  14. #14
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    In the attached file on the sheet Instructions & Results Summary cell D18 will be valued "D" or "F" which ideally would cause picCorrectAnswerImage or picWrongAnswerImage to be shown on sheet ST in range B16 to E41. The pics are on sheet PICS in cell A1 & A2.

    Thanks for the assist.
    Attached Files Attached Files

  15. #15
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    The sample file was a huge help!
    Try defining ShowMyPic as:
    Please Login or Register  to view this content.
    Notice: Because your tab name contains spaces, it must be enclosed in single quotes.

    Then...
    Select the cell under one of the images
    Home.Copy...Copy as picture...As shown when printed
    Select the destination cell where the image will be displayed
    Home.Paste...as picture
    While the picture is still selected
    Enter this in the formula bar: =ShowMyPic

    Does that help

  16. #16
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    I keep getting an invalid ref. message at the last step =ShowMyPic. I deleted everything started over etc.??

    Thanks for your patients.


    -j

  17. #17
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    I opened your posted file, followed the steps I listed, and the picture changed whenever I saved the file as something valid (containing a "D" or an "F")
    See attached file.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    I was able to clear the invaild ref. error now the pics don't change when the Cell D18 is cahnge back and forth from "D" to "F". ?

  19. #19
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    It works! Thanks so much - I feel bad taking so much of your time. I hope I can help others someday; I'd like to bring up my skills.

    The pics don’t update right away after the letter is change from F to D - I hit refresh no effect - save and reopen it works.

    I'm happy with it.

    Have a great week end.

    --J

  20. #20
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display Picture "A" or Picture "B" based on value in Cell A1

    Yes, I didn't go into the efficiency of the file, but it's acting like there's an extensive dependency tree that doesn't trigger value changes in a timely manner.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Show a picture based on a cell value - "Reference not valid" error
    By KevinJ25 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-19-2014, 11:35 AM
  2. Show a picture based on a cell value - "Reference not valid" error
    By KevinJ25 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2014, 06:00 AM
  3. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  4. Replies: 2
    Last Post: 10-29-2013, 01:15 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. To block "to "eliminate" of the picture of form, information
    By Isaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2006, 12:55 PM
  7. To block "to "eliminate" of the picture of form, information
    By Isaac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2006, 12:35 PM
  8. [SOLVED] To block "to "eliminate" of the picture of form, information
    By Isaac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2006, 11:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1