Excel 2009, Picture as per value

[GSN]

Hi.

How to make a picture appear depending on a value in a cell?
For example if the value is 1 then Picture no 1 is shown, and if the value is 2 then picture no one disappears and picture no 2 appears.

[teylyn]

Hello GSN,

welcome to the forum.

What Excel version are you interested in? Excel 2009 is not on my list.

[vikas.bhandari]

Hi there!

I assume you meant 2007. Anyways, I have created a, excel 2003 version file sample. I am sure it will work on your excel.

Thanks,
Vikas

[contaminated]

This task can be done by using excel OFFSET function.

STEP 1 Creating photoalbum.

On the first sheet of your workbook insert several images (say 3). Place in column A their names of countries (USA, Russia and Azerbaijan) and their flags next to names so that they would fit the cell, as you see in a screenshot.

Now you have to give a name to our list to refer to it in the future. Go to the Insert - Name - Define (Formulas > Defined Name Define Name), enter the name (eg DynamicAlbum) and in address field type this formulae:

Please Login or Register  to view this content.

This formula defines the last cell in column A and displays the output range from A1 to that found cell. This relatively complex structure is needed to subsequently build is needed to enlarge or reduce used range automatically, not thinking of correction range all time. If you know that this is a fixed range (e.g you won't add any item in ColumnA in the future), you can simply point =A1:A3 instead of this formula.

STEP 2 Create drop down list.

Go to second sheet, select A1 and go to Data > Validation (Data > Data Tools > Data Validation). Select list from Allow drop-down and in Sorce field press F3 and select defined name DynamicAlbum.

It is convinent to name the cell with validation list. Lets name it to DropDown.

Please Login or Register  to view this content.

Now you need to copy an image. To do that select the cell with the first photo and hold Shift key, click the Edit menu. There should now be an unseen before, Copy image command: Click on it (for xl2007 users select the cell with the first photo got Home > Clipboard > Paste > As Picture > Copy as Picture).
Copy it and paste it on to second sheet in any blank cell.

STEP 4 Creating Dynamic Reference to the phtolist.

Now you need to make a dynamic link to the photo.

Go to the Insert - Name - Define (Formulas > Defined Name Define Name), enter the name (eg PhotoList) and in address field type this formulae:

Please Login or Register  to view this content.

STEP 5 Tie the photo to the named formulae

The only thing that remains, is to assign this name to the picture in second sheet sheet.
Just select the picture and type in the formula bar

Please Login or Register  to view this content.

And Press Enter.

SEE ATTACHED FILE

[GSN]

Hi.
Yes I meant 2007.
The sample does require you to push a button, but I want it to go automatically.
I have 3 selections from a drop down menu that together will give a number. This number should then automatically retune a picture as illustrated in attached picture.

Regards

Gert

Hi there!

I assume you meant 2007. Anyways, I have created a, excel 2003 version file sample. I am sure it will work on your excel.

Thanks,
Vikas

[GSN]

Hi.

I menant 2007.

Tanks.
Gert