Showing image based on value in a cell

**lukestkd** · 02-09-2013, 07:43 PM

Hi all,
I am trying to create a seating plan wb for managing my class data. File here: Seating Plan File Dummy Data v04.xlsm

Alan has kindly helped me sort the formulas for the data. I now need to find a formula which will match the student name to an image. Example for attached wb:

B7 shows "ClassAStudent9" so the image object next to the 1 would return the image in sheet/cell 'Students'!B6

I tried following this: YouTube video which used a name with this formula

Please Login or Register  to view this content.

and a bitmap image object looking up the name.

However this kept returning an error message saying the reference isn't valid.

I have placed three objects as an example of where the images will be placed. I will replicate the code for each image if someone can help me solve this!

Thanks in advance

Luke

**benishiryo** · 02-09-2013, 10:05 PM

hi Luke, i like Excelisfun too. =)

you have to fix your cell references in Named Range. otherwise, it will move. so you formula should be:
=INDEX(Students!$B$1:$B$6,MATCH('Seating Plan'!$B$7,Students!$A$1:$A$6,0))

then press CTRL + F3 to name it. I named it "Pic". after that, go to Insert-> Object

go to the formula bar & put in the name above.

**lukestkd** · 02-10-2013, 07:32 AM

Originally Posted by benishiryo

=INDEX(Students!$B$1:$B$6,MATCH('Seating Plan'!$B$7,Students!$A$1:$A$6,0))

Your formula is working perfectly on the file you attached. However, whenever I try to replicate for my file Excel keeps showing an error message saying reference isn't valid.

I have attached the file [ATTACH]here[/ATTACH](the named range looks up columns C and D now as I have added two columns in for student data).

**lukestkd** · 02-10-2013, 06:24 PM

I don't understand why it wasn't working on my file, but I've just used yours and applied the formulas for all the relevant cells - working like a dream. If you bust the solution for not working on my file let me know! Thank you

Luke

**benishiryo** · 02-10-2013, 09:48 PM

you're very welcome. i think it might be a 2010 problem. your file works fine in my computer with excel 2007. instead of using Objects, try copying any of the picture from the "Students" sheet & paste it inside "Seating Plan" sheet. then name the picture accordingly like how you did for the Objects.