Ron,
Thanks for the response, Here's exactly what I've created as a trial
experiment:
3 worksheets: Sheet one contains a drop down list created using data
validation in cell A5. I want the picture that matches the selction from that
to be displayed in cell A2 of that same sheet, sheet 1. On Sheet two I have
three pictures of boats. Picture one is placed over cells A1-D6 (
=Sheet2!$A$1:$D$6) and is named "picOne". Picture two is placed over cells
A7- D12 (=Sheet2!$A$7:$D$12) and is named "picTwo". Picture three is placed
over cells A13- D18 (=Sheet2!$A$13:$D$18) and is named "picThree". Finally on
my third worksheet I have my list of names (One, Two, and Three) on cells
A1-A3 used for my data validation on sheet 1.
"Ron Coderre" wrote:
I'll try to address the method I posted.
With a value in the drop down list, you should be able to:
EditGo to
In the reference field, type: ShowMyPic
Click the [OK] button
That should bring you to the range that contains the picture that is
referenced.
If it does not, then the problem is in your definition of ShowMyPic
Example:
with this definition...
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel,
then ShowMyPic will refer to that range.
In order to help, I'll need a little more information:
1)What is the exact location of your dropdown list (sheet name and cell
address)?
2)What is the exact name you gave to one of the pictures?
***********
Regards,
Ron
XL2002, WinXP-Pro
"Mike at Channel" wrote:
I'm working at a Marina and trying to make a comparison chart for different
types of boats. I am trying to make a list, and based upon the users
selection of a boat, insert a corresponding picture to match the boat
selected from that list. I've researched other thread discussions, and tried
to approach it this way, as reccommended by Ron Coder
Select Sheet2 and turn off Grid Lines
(ToolsOptionsView tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. InsertPicture from file. (select picture, put it in the sheet and
resize it).
1b. Select the range of cells that contains 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 on cells A2:D10:
Select those cells
InsertNameDefine
Name: picElephant
2)Build your data validation list on a cell in Sheet1 and pick one of the
items.
If you need help: use Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html
3)Create a dynamic range name that refers to that cell:
InsertNameDefine
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
...or whatever cell you chose.
4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.
5)With picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic
After trying this method, instead of getting the picture inserted, I get the
name of the cells that the picture is in...(the name I gave the cells that
contained the picture). It does change according to my selection from the
list, but doesnt display the picture. The text that is returned instead of
the picture seems distorted as well. Any ideas of what I could be doing
wrong? I've also tried making this work through macros which I am even
less familiar with. I was able to get that to work for one selection, but I
dont know how to properly code it to work for every boat in my list. I used
this code reccommended by Bernie Deitrick:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count < 1 Then Exit Sub
If Target.Address < "$A$1" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("B9").Select
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\PHIL\My Documents\My
Pictures\test.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub
I assume I would need to somehow nest this or create If - Else commands?
Also to clarify, I did change the file names and paths to those that matched
my project. It worked for one selection, but I don't know how to code it for
a list of say 40 boats
Any suggestions of the easiest way to go about doing this? Ive been trying
for a few days now, and could use any help I can get!