Hi there,

Lets say in Sheet 2 I had pasted 3 pictures

On sheet 1:

A1=1
A2=2

Is it possible to put a formula in B1 that has this logic:

If A1=1 then show picture 1, otherwise show picture 3

I doubt its possible, but you never know eh.



or, if I have 3 images saved on my harddrive, can I use a formula to show
the image in a spreadsheet without having to insert/paste it in?

Many thanks,

AJ

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

For an alternative non-VBA solution, try this:

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.

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


"AJ" wrote:

Hi there,

Lets say in Sheet 2 I had pasted 3 pictures

On sheet 1:

A1=1
A2=2

Is it possible to put a formula in B1 that has this logic:

If A1=1 then show picture 1, otherwise show picture 3

I doubt its possible, but you never know eh.



or, if I have 3 images saved on my harddrive, can I use a formula to show
the image in a spreadsheet without having to insert/paste it in?

Many thanks,

AJ

Hi,

Thanks for getting back to me.

It's a good answer, but not quite what I'm looking for.

I'll explain exactly what I'm doing:

I'm studying Japanese and I'm wanting to create a spreadsheet which randomly
selects a number of images from the alphabet and then I'll test to see if I
can read them.

Let's say I copy and paste 45 images of Japanese symbols on to Sheet 2.

on sheet 1, cells A1:K1 contain a formula which shows random whole numbers
between 1 and 45 (these values change each time I press F9/re-calculate the
sheet)

on sheet 1, cells B1:K1 contain a formula which results in an image
depending on what value is shown in the cell above it.

So, whenever I press F9/recalculate the sheet, new images are instantly
placed in cells B1:K1 for me to try and read.

Hope that makes sense.

Thanks again,

AJ


"Ron Coderre" wrote:

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

For an alternative non-VBA solution, try this:

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.

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


"AJ" wrote:

Hi there,

Lets say in Sheet 2 I had pasted 3 pictures

On sheet 1:

A1=1
A2=2

Is it possible to put a formula in B1 that has this logic:

If A1=1 then show picture 1, otherwise show picture 3

I doubt its possible, but you never know eh.



or, if I have 3 images saved on my harddrive, can I use a formula to show
the image in a spreadsheet without having to insert/paste it in?

Many thanks,

AJ

I meant B2:K2 (not B1:K1 as previously stated)

"AJ" wrote:

Hi,

Thanks for getting back to me.

It's a good answer, but not quite what I'm looking for.

I'll explain exactly what I'm doing:

I'm studying Japanese and I'm wanting to create a spreadsheet which randomly
selects a number of images from the alphabet and then I'll test to see if I
can read them.

Let's say I copy and paste 45 images of Japanese symbols on to Sheet 2.

on sheet 1, cells A1:K1 contain a formula which shows random whole numbers
between 1 and 45 (these values change each time I press F9/re-calculate the
sheet)

on sheet 1, cells B1:K1 contain a formula which results in an image
depending on what value is shown in the cell above it.

So, whenever I press F9/recalculate the sheet, new images are instantly
placed in cells B1:K1 for me to try and read.

Hope that makes sense.

Thanks again,

AJ


"Ron Coderre" wrote:

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

For an alternative non-VBA solution, try this:

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.

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


"AJ" wrote:

Hi there,

Lets say in Sheet 2 I had pasted 3 pictures

On sheet 1:

A1=1
A2=2

Is it possible to put a formula in B1 that has this logic:

If A1=1 then show picture 1, otherwise show picture 3

I doubt its possible, but you never know eh.



or, if I have 3 images saved on my harddrive, can I use a formula to show
the image in a spreadsheet without having to insert/paste it in?

Many thanks,

AJ

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

For an alternative non-VBA solution, try this:

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.

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


Hi Ron,

That's a great technique.
Is it a documented feature or what?
If it is documented what's it called?

Ken Johnson

Honestly, Ken......I read that technique way back around 1995 in one of the
Excel newsletters I subscribed to (possibly "Inside Excel"). I must have
liked it because I remembered all the details and tend to use it if the only
vba in the project would be to drive the picture engine.

Thanks for asking!

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

XL2002, WinXP


"Ken Johnson" wrote:


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

For an alternative non-VBA solution, try this:

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.

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


Hi Ron,

That's a great technique.
Is it a documented feature or what?
If it is documented what's it called?

Ken Johnson

Ron Coderre wrote:
Honestly, Ken......I read that technique way back around 1995 in one of the
Excel newsletters I subscribed to (possibly "Inside Excel"). I must have
liked it because I remembered all the details and tend to use it if the only
vba in the project would be to drive the picture engine.

Thanks for asking!

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

XL2002, WinXP



Thanks for that Ron, I'm sure I'll get plenty of use out of that
technique.
Another thing I really like about it is that instead of showing a
picture you can show a part of the other worksheet inside the active
sheet.

Thanks again.

Ken Johnson