SOLVED

Show images conditionally

%3CLINGO-SUB%20id%3D%22lingo-sub-72144%22%20slang%3D%22en-US%22%3EShow%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-72144%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20show%20some%20images%20in%20my%20Excel%20spreadsheet%20only%20when%20certain%20conditions%20are%20valid.%3C%2FP%3E%3CP%3EFor%20example%3A%20Only%20show%20the%20image%20when%20A1%26gt%3B10%20or%20B4%26lt%3B5.%3C%2FP%3E%3CP%3EHow%20should%20I%20do%20this%3F%3C%2FP%3E%3CP%3EGosse%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-72144%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-72674%22%20slang%3D%22en-US%22%3ERE%3A%20Show%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-72674%22%20slang%3D%22en-US%22%3EThanx%20for%20your%20replies%2C%20it%20helps%20me%20a%20lot!%20The%20replies%20of%20Sergei%20Baklan%20and%20Faysal%20Al%20Farooqui%20are%20very%20similar%2C%20but%20the%20combination%20of%20both%20replies%20made%20me%20understand%20it%20even%20better.%20Thank%20you%20all%20very%20much.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-72660%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-72660%22%20slang%3D%22en-US%22%3E%3CP%3E1.%20In%20C1%20cell%20use%20%3DIF(AND(A1%26gt%3B10%2CB1%26lt%3B5)%2CCHOOSE(RANDBETWEEN(1%2C3)%2C%22mb_1%22%2C%22mb_2%22%2C%22mb_3%22)%2C%22x%22)%20this%20formula%20to%20generate%20%22mb_1%22%2C%20%22mb_2%22%20and%20%22mb_3%22%20three%20images%20name%20based%20on%20your%20condition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Select%20C1%20cell%20and%20click%20on%20Formula%20%7C%20Define%20Name%20and%20type%3A%20%22ShowPhoto%22%20as%20name%20and%20%3DINDIRECT(Sheet1!%24C%241)%20as%20refers%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20Insert%203%20marble%20ball%20images%20into%20E11%2C%20F11%2C%20G11%20and%20H11.%20And%20name%20these%20cells%20as%20%22mb_1%22%2C%20%22mb_2%22%2C%20%22mb_3%22%20and%20%22x%22%20and%20refers%20to%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20E11%2C%20%3DSheet1!%24E%2411%2C%20For%20F11%2C%20%3DSheet1!%24F%2411%20For%20G11%2C%20%3DSheet1!%24G%2411%20For%20H11%2C%20%3DSheet1!%24H%2411%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Select%20E11%20cell%20and%20Click%20on%20Home%20%7C%20Copy%20%7C%20Copy%20as%20picture.%20While%20a%20dialog%20box%20appear%2C%20Click%20on%20%22Ok%22%20to%20copy%20it.%20Now%20press%20Ctrl%2BV%20on%20that%20sheet%20to%20paste%20the%20image.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E5.%20Select%20the%20pasted%20image%20and%20enter%20%3DShowPhoto%20in%20the%20formula%20bar%20to%20link%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E6.%20Now%20change%20the%20value%20A1%20or%20B1%20to%20change%20the%20condition%20of%20C1%20which%20will%20generate%20a%20image%20name%20and%20pasted%20image%20will%20change%20the%20image%20like%20below%20image%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20804px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F14983i711A80EA0D06D85D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22gif14.gif%22%20title%3D%22gif14.gif%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EImage%3A%20Changing%20the%20image%20based%20on%20condition%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20I%20have%20attached%20the%20whole%20working%20file.%20So%20that%20you%20can%20try%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20more%20reference%2C%20an%20article%20on%20below%20link%20will%20helpful%3A%3C%2FP%3E%3CP%3E%3CA%20title%3D%22http%3A%2F%2Fafaysal.blogspot.com%2F2017%2F05%2Fhow-to-display-image-in-worksheet-based.html%22%20href%3D%22http%3A%2F%2Fafaysal.blogspot.com%2F2017%2F05%2Fhow-to-display-image-in-worksheet-based.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fafaysal.blogspot.com%2F2017%2F05%2Fhow-to-display-image-in-worksheet-based.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20will%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-72589%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-72589%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gosse%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT010450_Displaying_Images_based_on_a_Result.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT010450_Displaying_Images_based_on_a_Result.html%3C%2FA%3E%20tip%20could%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-72217%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-72217%22%20slang%3D%22en-US%22%3EWhat%20kind%20of%20images%3F%20Have%20you%20looked%20at%20Conditional%20formatting%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399409%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20images%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F56895%22%20target%3D%22_blank%22%3E%40g.vanderveen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReview%20this%20example%20that%20image%20added%20based%20on%20cell%20value%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20this%20template%2C%20when%20a%20cell%20in%20column%20A%20is%20selected%2C%20if%20there%20is%20picture%20of%20the%20cell%20contents%20is%20automatically%20shown%20on%20column%20F.%20The%20selected%20cell%20is%20also%20indicated%20by%20the%20arrow%20shape%20that%20we%20have%20created%20with%20VBA%20codes.%20Code%20snippet%20only%20was%20added%20to%20Worksheet_SelectionChange%20procedure%20for%20picture%20viewing.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Display_picture_based_cell_1.gif%22%20style%3D%22width%3A%20968px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284901iBDE2A5CCD1AA9AAB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Display_picture_based_cell_1.gif%22%20alt%3D%22Display_picture_based_cell_1.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERead%20more%20and%20download%20sample%20file%20here%20%3A%20%3CA%20href%3D%22https%3A%2F%2Feksi30.com%2Fexcel-display-image-based-on-cell-value%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20display%20image%20based%20on%20cell%20value%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like to show some images in my Excel spreadsheet only when certain conditions are valid.

For example: Only show the image when A1>10 or B4<5.

How should I do this?

Gosse

5 Replies
What kind of images? Have you looked at Conditional formatting?
best response confirmed by g.vanderveen (New Contributor)

1. In C1 cell use =IF(AND(A1>10,B1<5),CHOOSE(RANDBETWEEN(1,3),"mb_1","mb_2","mb_3"),"x") this formula to generate "mb_1", "mb_2" and "mb_3" three images name based on your condition.

 

2. Select C1 cell and click on Formula | Define Name and type: "ShowPhoto" as name and =INDIRECT(Sheet1!$C$1) as refers to.

 

3. Insert 3 marble ball images into E11, F11, G11 and H11. And name these cells as "mb_1", "mb_2", "mb_3" and "x" and refers to as below:

 

For E11, =Sheet1!$E$11, For F11, =Sheet1!$F$11 For G11, =Sheet1!$G$11 For H11, =Sheet1!$H$11

 

4. Select E11 cell and Click on Home | Copy | Copy as picture. While a dialog box appear, Click on "Ok" to copy it. Now press Ctrl+V on that sheet to paste the image.

 

5. Select the pasted image and enter =ShowPhoto in the formula bar to link it.

 

6. Now change the value A1 or B1 to change the condition of C1 which will generate a image name and pasted image will change the image like below image:

 

Image: Changing the image based on conditionImage: Changing the image based on condition

 

Here I have attached the whole working file. So that you can try it.

 

For more reference, an article on below link will helpful:

http://afaysal.blogspot.com/2017/05/how-to-display-image-in-worksheet-based.html

 

Hope this will help you.

Thanx for your replies, it helps me a lot! The replies of Sergei Baklan and Faysal Al Farooqui are very similar, but the combination of both replies made me understand it even better. Thank you all very much.

@g.vanderveen 

Review this example that image added based on cell value : 

In this template, when a cell in column A is selected, if there is picture of the cell contents is automatically shown on column F. The selected cell is also indicated by the arrow shape that we have created with VBA codes. Code snippet only was added to Worksheet_SelectionChange procedure for picture viewing.

 

Display_picture_based_cell_1.gif

 

Read more and download sample file here : Excel display image based on cell value