Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP IMAGES | Excel Worksheet Functions | |||
display images | Excel Discussion (Misc queries) | |||
Images in Excel? | Excel Discussion (Misc queries) | |||
Excel DDE with images | Excel Discussion (Misc queries) | |||
If/Then statements with images??? | Excel Worksheet Functions |