VLOOKUP For Pictures?

[DanielWalters6]

Is it possible to look up the value in a cell, and depending on that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a value from
a combo box, it is then put into a cell, then a cell that is located next to
the drop down displays a description of the product. It does this by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a small
photograph of the product?

TIA For any help

 

[Ron Coderre]

The VBA solution provided by JE McGimpsey is the generally accepted BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[DanielWalters6]

Thank you.

This did work at the time.

Sorry it's taken so long to respond.


--
Dan Walters

The VBA solution provided by JE McGimpsey is the generally accepted BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Is it possible to look up the value in a cell, and depending on that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a value from
a combo box, it is then put into a cell, then a cell that is located next to
the drop down displays a description of the product. It does this by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a small
photograph of the product?

TIA For any help

 

[Ron Coderre]

Thank you for the update, Dan.....It's never too late to let someone know
that they helped.

***********
Regards,
Ron

XL2003, WinXP

Thank you.

This did work at the time.

Sorry it's taken so long to respond.


--
Dan Walters

The VBA solution provided by JE McGimpsey is the generally accepted BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Is it possible to look up the value in a cell, and depending on that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a value from
a combo box, it is then put into a cell, then a cell that is located next to
the drop down displays a description of the product. It does this by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a small
photograph of the product?

TIA For any help

 

[dlanier]

The VBA solution provided by JE McGimpsey is the generally accepted BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Is it possible to look up the value in a cell, and depending on that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a value from
a combo box, it is then put into a cell, then a cell that is located next to
the drop down displays a description of the product. It does this by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a small
photograph of the product?

TIA For any help

 

[dlanier]

Ron,
Sorry, didn't get to ask my question. How can you do a lookup to a cell
reference that will insert pictures that are in a
folder/c:mypictures/picture.wmf? I am creating a pricelist in excel that I
want to insert a picture file with a formula, so I want to lookup in cell C1
and go to my picture file and insert the picture that has the same name as
the lookup name in C1. There will be a different picture per line depending

Thank you for the update, Dan.....It's never too late to let someone know
that they helped.

***********
Regards,
Ron

XL2003, WinXP

Thank you.

This did work at the time.

Sorry it's taken so long to respond.


--
Dan Walters

The VBA solution provided by JE McGimpsey is the generally accepted BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Is it possible to look up the value in a cell, and depending on that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a value from
a combo box, it is then put into a cell, then a cell that is located next to
the drop down displays a description of the product. It does this by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a small
photograph of the product?

TIA For any help

 

[Ron Coderre]

The below code
. loops through each cell in the selected range
. reads the filepath from that cell
. inserts the referenced picture in the cell to the right of the cell:
. resizes the height and width of the picture to the cell's height

Sub InsertPicFromFile()
Dim cCell As Range

For Each cCell In Selection
If cCell.Value <> "" Then
On Error Resume Next
ActiveSheet.Shapes.AddPicture _
Filename:=cCell.Value, LinkToFile:=msoFalse,
SaveWithDocument:=msoTrue, _
Left:=cCell.Offset(ColumnOffset:=1).Left, Top:=cCell.Top,
Width:=cCell.Height, Height:=cCell.Height
End If
Next cCell
End Sub


To use that code:
1)Select a vertical range of cells containing complete paths to picture
files.
(Make sure the row heights are large enough to view the pictures.)

2)[Alt]+[F8].....shortcut for <tools><macro><macros>....Select:
InsertPicFromFile....Click [Run]

Is that something you can work with?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Ron,
Sorry, didn't get to ask my question. How can you do a lookup to a cell
reference that will insert pictures that are in a
folder/c:mypictures/picture.wmf? I am creating a pricelist in excel that
I
want to insert a picture file with a formula, so I want to lookup in cell
C1
and go to my picture file and insert the picture that has the same name as
the lookup name in C1. There will be a different picture per line
depending

Thank you for the update, Dan.....It's never too late to let someone know
that they helped.

***********
Regards,
Ron

XL2003, WinXP

Thank you.

This did work at the time.

Sorry it's taken so long to respond.


--
Dan Walters


:

The VBA solution provided by JE McGimpsey is the generally accepted
BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the
sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Is it possible to look up the value in a cell, and depending on
that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a
value from
a combo box, it is then put into a cell, then a cell that is
located next to
the drop down displays a description of the product. It does this
by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a
small
photograph of the product?

TIA For any help

 

[dlanier]

Thank you so much. This was a lot of help. I actually had to get my IT guy
to set this up, but he was able to use this to create what I needed. Thanks
for the fast response.

The below code
. loops through each cell in the selected range
. reads the filepath from that cell
. inserts the referenced picture in the cell to the right of the cell:
. resizes the height and width of the picture to the cell's height

Sub InsertPicFromFile()
Dim cCell As Range

For Each cCell In Selection
If cCell.Value <> "" Then
On Error Resume Next
ActiveSheet.Shapes.AddPicture _
Filename:=cCell.Value, LinkToFile:=msoFalse,
SaveWithDocument:=msoTrue, _
Left:=cCell.Offset(ColumnOffset:=1).Left, Top:=cCell.Top,
Width:=cCell.Height, Height:=cCell.Height
End If
Next cCell
End Sub


To use that code:
1)Select a vertical range of cells containing complete paths to picture
files.
(Make sure the row heights are large enough to view the pictures.)

2)[Alt]+[F8].....shortcut for <tools><macro><macros>....Select:
InsertPicFromFile....Click [Run]

Is that something you can work with?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Ron,
Sorry, didn't get to ask my question. How can you do a lookup to a cell
reference that will insert pictures that are in a
folder/c:mypictures/picture.wmf? I am creating a pricelist in excel that
I
want to insert a picture file with a formula, so I want to lookup in cell
C1
and go to my picture file and insert the picture that has the same name as
the lookup name in C1. There will be a different picture per line
depending

Thank you for the update, Dan.....It's never too late to let someone know
that they helped.

***********
Regards,
Ron

XL2003, WinXP


:

Thank you.

This did work at the time.

Sorry it's taken so long to respond.


--
Dan Walters


:

The VBA solution provided by JE McGimpsey is the generally accepted
BEST
approach:
http://www.mcgimpsey.com/excel/lookuppics.html


This is just an alternative if you don't want to use VBA:

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

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the
sheet).
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:
Insert>Name>Define
Name: picElephant

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:
Insert>Name>Define
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 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.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Is it possible to look up the value in a cell, and depending on
that display
a small graphic from a table elsewhere in the spreadsheet.

At the moment, I have a small "system" which when a user picks a
value from
a combo box, it is then put into a cell, then a cell that is
located next to
the drop down displays a description of the product. It does this
by looking
up the value that's stored in the cell, within a VLOOKUP statement.

Is it possible to do this with photographs - could I include a
small
photograph of the product?

TIA For any help

 

[Ron Coderre]

You're very welcome.....I'm glad I could help.

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Thank you so much. This was a lot of help. I actually had to get my IT
guy
to set this up, but he was able to use this to create what I needed.
Thanks
for the fast response.

The below code
. loops through each cell in the selected range
. reads the filepath from that cell
. inserts the referenced picture in the cell to the right of the cell:
. resizes the height and width of the picture to the cell's height

Sub InsertPicFromFile()
Dim cCell As Range

For Each cCell In Selection
If cCell.Value <> "" Then
On Error Resume Next
ActiveSheet.Shapes.AddPicture _
Filename:=cCell.Value, LinkToFile:=msoFalse,
SaveWithDocument:=msoTrue, _
Left:=cCell.Offset(ColumnOffset:=1).Left, Top:=cCell.Top,
Width:=cCell.Height, Height:=cCell.Height
End If
Next cCell
End Sub


To use that code:
1)Select a vertical range of cells containing complete paths to picture
files.
(Make sure the row heights are large enough to view the pictures.)

2)[Alt]+[F8].....shortcut for <tools><macro><macros>....Select:
InsertPicFromFile....Click [Run]

Is that something you can work with?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)