We help IT Professionals succeed at work.

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

maryj152
maryj152 asked
on
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.
Comment
Watch Question

Commented:
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
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)
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

Author

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?

Author

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?

Author

Commented:
forgot to attach file
Insert-picture-function-1.ods
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.

Author

Commented:
I will have to wait till I have to Excel in the next few days.

Author

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?
The function must be in a module, not in a sheet module.
Use the menu Insert, and insert a module.

Author

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?
As long as you keep the scope I don't see any problem in follow up.