display picture in one cell based on value in another cell in excel

How do I insert a picture into an Excel cell depending on the value in a different cell?
IF(B1="Steve", picture of Steve)
The pictures will be in a separate sub folder but in the same folder as the Excel file.
maryj152Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
I don't think that is doable by in-cell formula
the only way is using VBA to insert picture based on the name
any other option like have it as OLE will be impractical
mark_harris231Commented:
It would be possible, by formula, to build hyperlinks to the image, if displaying the image in the spreadsheet wasn't absolutely required (this might even be desirable to keep the workbook "light"):

=HYPERLINK("\\yournetworklocation\"&A2&".bmp",A2&"'s pic")

This would yield a link reading "Steve's pic"  (assuming A2 = "Steve")

Assumptions:
- images are named in some convention that can be incorporated into the logic (e.g., Steve = Steve.bmp), or could be referenced in a table that matches name to file name of image

- All users of the spreadsheet have permissions to "yournetworklocation" where the images are stored

- Users have the required and associated software to open the image file type(s)
Ejgil HedegaardCommented:
Try this VBA function, works like a formula, and can be copied just like a formula.
The function InsertPictureInCell use 2 arguments.
1. The cell to put the picture in, typically the cell with the formula.
2. Full path to the picture.
See cell D2.

In the example the name "Steve" is in A2, sub folder name "Pictures" in B2 and the picture type "gif" in C2.
The path to the file is made in the function using the Excel file path + Picturefolder as subfolder + Name + Picturetype.
If folder name and picture type is known it can be set in the function, and column B and C is not needed.

If a picture exist in the cell, nothing happens.
Delete the picture in D2.
Set the folder name in B2, and the picture type in C2.
Press Ctrl+Shift+Alt+F9 to recalculate full.
If the file is not found the function displays "No file" in D2.
Insert-picture-function.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

maryj152Author Commented:
working from home due to ice storm.
going to try using OpenOffice instead of Excel
Any problems that I  might need to deal with?
maryj152Author Commented:
I opened the file using Open Office and saved in the same folder with the images.
When I put in the folder name  for images (unit2images) in B2 I still get the #NAME error
Since all images will be .png and in the same folder, I tried with the literal folder name and without  C2 and got #NAME error.
What am I missing?
maryj152Author Commented:
forgot to attach file
Insert-picture-function-1.ods
Ejgil HedegaardCommented:
When I open the file in Excel, there is a warning about unreadable content.
After repair there are #NAME in cells D2 and E2, but no formulas.
The VBA module with the function is not there.
I don't think Open Office can use Excel VBA.
maryj152Author Commented:
I will have to wait till I have to Excel in the next few days.
maryj152Author Commented:
Did some experimenting with your file and got it working. When i start typing =insert..... it popped up as a function to choose and then fill in the parameters.
I copied your function to my VisualBasic for the spreadsheet where I want to use it, but I don't get is as an option when I start with =.
Is there some special way/place it is to be saved so that I can call it?
Ejgil HedegaardCommented:
The function must be in a module, not in a sheet module.
Use the menu Insert, and insert a module.
maryj152Author Commented:
Thank you for your patience. Haven't had the opportunity to try the insert module yet.
Can I follow up with other questions after a solution is accepted?
Ejgil HedegaardCommented:
As long as you keep the scope I don't see any problem in follow up.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.