Vlookup with Images
 

I'm currently using vlookup to select paragraphs of information depending on
a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Mike

Yes, there is a way.

You can employ event code and VLOOKUP with Data Validation list selection
to choose which picture to show?

See JE McGimpsey's site for more on lookuppics and a sample workbook.

http://www.mcgimpsey.com/excel/lookuppics.html


Gord Dibben MS Excel MVP

On Sat, 2 Jun 2007 10:30:56 +1000, "Mike Langensiepen"
wrote:

I'm currently using vlookup to select paragraphs of information depending on
a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

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

However, as a new user, if you're not quite ready to jump into the VBA
programming pool this is an alternative non-VBA solution:

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

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 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:
InsertNameDefine
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:
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 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.

Is that something you can work with?

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

XL2002, WinXP


"Mike Langensiepen" wrote:

I'm currently using vlookup to select paragraphs of information depending on
a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

As coded, John's lookuppics allows for only one picture at a time to be
presented.

Bernie Dietrick sent me a slick workbook that allows multiple pictures to be
presented and selected from DV dropdowns.

Would be nice if one of the regulars with a site could provide this alternative
to John's lookuppics.

That would be dependent upon Bernie's permission, of course.

John, Ron, Bob, Debra.........anyone?


Gord Dibben MS Excel MVP

On Fri, 1 Jun 2007 20:25:01 -0700, Ron Coderre
wrote:

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

Vlookup with Images
 

If Bernie is interested, I could make it available on my site.

Gord Dibben wrote:
As coded, John's lookuppics allows for only one picture at a time to be
presented.

Bernie Dietrick sent me a slick workbook that allows multiple pictures to be
presented and selected from DV dropdowns.

Would be nice if one of the regulars with a site could provide this alternative
to John's lookuppics.

That would be dependent upon Bernie's permission, of course.

John, Ron, Bob, Debra.........anyone?


Gord Dibben MS Excel MVP

On Fri, 1 Jun 2007 20:25:01 -0700, Ron Coderre
wrote:


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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Vlookup with Images
 

Hi,

Here's a non vba solution from Jan Karel Pieterse.
http://www.jkp-ads.com/Articles/ShowPicture00.htm

Cheers
Andy

Mike Langensiepen wrote:
I'm currently using vlookup to select paragraphs of information depending on
a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Thanks Debra.

Re-posted in private.news hoping to catch Bernie over there.


Gord

On Sat, 02 Jun 2007 11:44:20 -0400, Debra Dalgleish
wrote:

If Bernie is interested, I could make it available on my site.

Gord Dibben wrote:
As coded, John's lookuppics allows for only one picture at a time to be
presented.

Bernie Dietrick sent me a slick workbook that allows multiple pictures to be
presented and selected from DV dropdowns.

Would be nice if one of the regulars with a site could provide this alternative
to John's lookuppics.

That would be dependent upon Bernie's permission, of course.

John, Ron, Bob, Debra.........anyone?


Gord Dibben MS Excel MVP

On Fri, 1 Jun 2007 20:25:01 -0700, Ron Coderre
wrote:


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

Vlookup with Images
 

Thanks for the solutions offered. A non-VBA multi-event version is what I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
...
I'm currently using vlookup to select paragraphs of information depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Sorry Gord, I should have been more specific

VBA is OK if I don't have to modify code too much - my programming skills
terminated at QuickBasic!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Bernie's solution that I spoke about involves VBA code.

There is no multi-event version without employing VBA.

What is the objection to to VBA?


Gord

On Tue, 5 Jun 2007 11:11:26 +1000, "Mike Langensiepen"

wrote:

Thanks for the solutions offered. A non-VBA multi-event version is what I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
...
I'm currently using vlookup to select paragraphs of information
depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

McGimpsey Version
 

OK I can see how this is done (even if I couldn't create it myself) but how
the heck do I display the hidden pictures? Also is there an easy way of
storing the pics in another sheet (I like to keep my data entry sheets as
clean as possible)

Thanks and apologies if the question is stupid

Cheers

Mike



"Mike Langensiepen" wrote in message
...
I'm currently using vlookup to select paragraphs of information depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Mike

Bernie's solution that I spoke about involves VBA code.

There is no multi-event version without employing VBA.

What is the objection to to VBA?


Gord

On Tue, 5 Jun 2007 11:11:26 +1000, "Mike Langensiepen"
wrote:

Thanks for the solutions offered. A non-VBA multi-event version is what I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
...
I'm currently using vlookup to select paragraphs of information depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Mike

VBA is a very powerful feature of the Office Applications.

Bernie's workbook involves event code which would not have to be changed in any
way.

The only changes to make would be to the DV dropdowns items and load in your
choices of images.

You can download from Debra Dalgleish's site

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection -- Select a clipart item from a data validation
dropdown list, and that picture appears in the adjacent cell. Uses
Worksheet_Calculate event code. Sample file from Bernie Deitrick.
ClipArtEvent.zip 30kb 03-Jun-07


Gord


On Tue, 5 Jun 2007 12:02:50 +1000, "Mike Langensiepen"
wrote:

Sorry Gord, I should have been more specific

VBA is OK if I don't have to modify code too much - my programming skills
terminated at QuickBasic!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Mike

Bernie's solution that I spoke about involves VBA code.

There is no multi-event version without employing VBA.

What is the objection to to VBA?


Gord

On Tue, 5 Jun 2007 11:11:26 +1000, "Mike Langensiepen"

wrote:

Thanks for the solutions offered. A non-VBA multi-event version is what I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
...
I'm currently using vlookup to select paragraphs of information
depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

McGimpsey Version
 

In article ,
"Mike Langensiepen" wrote:

OK I can see how this is done (even if I couldn't create it myself) but how
the heck do I display the hidden pictures?

One way: In the VBE's Immediate Window, enter

ActiveSheet.Pictures.Visible = True


Also is there an easy way of
storing the pics in another sheet (I like to keep my data entry sheets as
clean as possible)

Pictures are displayed only on the drawing layer of the sheet they're
inserted into.

McGimpsey Version
 

One way: In the VBE's Immediate Window, enter

ActiveSheet.Pictures.Visible = True


Thanks for this

Also is there an easy way of
storing the pics in another sheet (I like to keep my data entry sheets as
clean as possible)

Pictures are displayed only on the drawing layer of the sheet they're
inserted into.

Bugger! - I need about 1200 images each of 300x300 pixels!

Might be cut n paste after all.

Vlookup with Images
 

Thanks Gord. Am I correct in saying that like the McGimpsey solution, this
has to have the images on the same page?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

VBA is a very powerful feature of the Office Applications.

Bernie's workbook involves event code which would not have to be changed
in any
way.

The only changes to make would be to the DV dropdowns items and load in
your
choices of images.

You can download from Debra Dalgleish's site

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection -- Select a clipart item from a data validation
dropdown list, and that picture appears in the adjacent cell. Uses
Worksheet_Calculate event code. Sample file from Bernie Deitrick.
ClipArtEvent.zip 30kb 03-Jun-07


Gord


On Tue, 5 Jun 2007 12:02:50 +1000, "Mike Langensiepen"

wrote:

Sorry Gord, I should have been more specific

VBA is OK if I don't have to modify code too much - my programming skills
terminated at QuickBasic!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Mike

Bernie's solution that I spoke about involves VBA code.

There is no multi-event version without employing VBA.

What is the objection to to VBA?


Gord

On Tue, 5 Jun 2007 11:11:26 +1000, "Mike Langensiepen"

wrote:

Thanks for the solutions offered. A non-VBA multi-event version is what
I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
.. .
I'm currently using vlookup to select paragraphs of information
depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from
a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike

Vlookup with Images
 

Correct..........same page.

Stick them waaayyyy over there somwhere like column IT.


Gord

On Wed, 6 Jun 2007 15:19:53 +0800, "Mike Lang"
wrote:

Thanks Gord. Am I correct in saying that like the McGimpsey solution, this
has to have the images on the same page?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Mike

VBA is a very powerful feature of the Office Applications.

Bernie's workbook involves event code which would not have to be changed
in any
way.

The only changes to make would be to the DV dropdowns items and load in
your
choices of images.

You can download from Debra Dalgleish's site

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection -- Select a clipart item from a data validation
dropdown list, and that picture appears in the adjacent cell. Uses
Worksheet_Calculate event code. Sample file from Bernie Deitrick.
ClipArtEvent.zip 30kb 03-Jun-07


Gord


On Tue, 5 Jun 2007 12:02:50 +1000, "Mike Langensiepen"

wrote:

Sorry Gord, I should have been more specific

VBA is OK if I don't have to modify code too much - my programming skills
terminated at QuickBasic!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Bernie's solution that I spoke about involves VBA code.

There is no multi-event version without employing VBA.

What is the objection to to VBA?


Gord

On Tue, 5 Jun 2007 11:11:26 +1000, "Mike Langensiepen"

wrote:

Thanks for the solutions offered. A non-VBA multi-event version is what
I
need so I'll either wait for Bernie or try the Jan karel solution.

Cheers

Mike

"Mike Langensiepen" wrote in message
. ..
I'm currently using vlookup to select paragraphs of information
depending
on a dropdown list. The vlookup refers to an array in another sheet.

Is there any way of showing an image as well so that when I pick from
a
dropdown list, it selects an image from one of the cells in the lookup
array?

Thanks

Mike