I would like to write a very short story in Excel 2003 that has blanks
interspersed throughout the story. When a child fills in a blank with an
intuitive word (her own name, name of her pet, name of her brother, etc.) a
picture of her, or her pet, etc. pops up somewhere in the spreadsheet close
to the story. For instance, if she types in the name Spot, a picture of Spot
pops up. I had a sample spreadsheet that did exactly this, but lost it when
my hard drive crashed. I don't remember what function was used for the
blanks. I do remember the pictures were on a seperate spreadsheet from the
story. The formula for the blanks referenced the cells of the pictures. Can
anyone help? Thanks so much. PS. I am sllightly experienced with Excel. --
S Jan

On Sep 21, 1:04*pm, Sagit wrote:
I would like to write a very short story in Excel 2003 that has blanks
interspersed throughout the story. *When a child fills in a blank with an
intuitive word (her own name, name of her pet, name of her brother, etc.) a
picture of her, or her pet, etc. pops up somewhere in the spreadsheet close
to the story. *For instance, if she types in the name Spot, a picture of Spot
pops up. *I had a sample spreadsheet that did exactly this, but lost it when
my hard drive crashed. *I don't remember what function was used for the
blanks. *I do remember the pictures were on a seperate spreadsheet from the
story. *The formula for the blanks referenced the cells of the pictures.. *Can
anyone help? *Thanks so much. *PS. I am sllightly experienced with Excel. *--
S Jan

Try here...

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

Ken Johnson

Another way is DV0049 - ClipArt Selection at…
http://www.contextures.on.ca/excelfiles.html#DataVal

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

Here’s Ron Coderre’s non-VBA solution…
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.

Ken Johnson

I don't think my first reply went thru, so I am writing another one.
First, thanks so much for your help. I've never done a data validation
list, so I'm on a learning curve in followiing your instructions. I'm using
the Help file along with your excellent instructions. I see a lot of
applications for a drop down list.

When you mentioned the Define Name function, that rang a bell. I believe
the original method involved IF statements for each fill-in blank in the
story. I remember defining a name for each picture on sheet2. As the child
reads the story, encounters a blank, she must fill it in with the correct
name. That way, the child had to use her logical skills on selecting the
correct name and spelling it correctly. As she continues to read the story
and comes to another blank, she fills that in with another name, and another
picture pops up. And so on. The story gives clues as to what name belongs in
each blank. I just can't remember how to construct the IF statement to refer
to the correct picture on sheet2 for each blank.

--
S Jan


"Ken Johnson" wrote:


Another way is DV0049 - ClipArt Selection at€¦
http://www.contextures.on.ca/excelfiles.html#DataVal

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

Heres Ron Coderres non-VBA solution€¦
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.

Ken Johnson

On Sep 22, 7:40*am, Sagit wrote:
I don't think my first reply went thru, so I am writing another one.
First, thanks so much for your help. *I've never done a data validation
list, so I'm on a learning curve in followiing your instructions. *I'm using
the Help file along with your excellent instructions. *I see a lot of
applications for a drop down list.

When you mentioned the Define Name function, that rang a bell. *I believe
the original method involved IF statements for each fill-in blank in the
story. *I remember defining a name for each picture on sheet2. *As the child
reads the story, encounters a blank, she must fill it in with the correct
name. *That way, the child had to use her logical skills on selecting the
correct name and spelling it correctly. *As she continues to read the story
and comes to another blank, she fills that in with another name, and another
picture pops up. *And so on. The story gives clues as to what name belongs in
each blank. *I just can't remember how to construct the IF statement to refer
to the correct picture on sheet2 for each blank.

--
S Jan


I personally prefer using VBA to control when and where pictures are
to appear on a worksheet.
The worksheet is able to automatically run code whenever the reader
enters something into a cell. The code could simply control the
visibility of pictures already on the sheet. Only those pictures whose
correct name has been entered into the appropriate cell would be made
visible. Perhaps if a cell contains an incorrect (but not blank) entry
the code could pop up a message box asking the reader to try again.
The code could clear that cell after the message box's OK button is
clicked.

These are only my ideas at the moment. I will have a go at it as soon
as time permits.

Ken Johnson

Here's my version of your idea...

http://members.iinet.net.au/~hanjohn/Picture Story.xls

I have used VBA so you will be prompted to Enable Macros when it is
opened.

Thanks for the idea.

Ken Johnson

That last link failed. Try this...

http://members.iinet.net.au/~hanjohn...re%20Story.xls

Ken Johnson