Hello guys , for those having a "reference isn't Valid" error , try removing(or replace by underscore) the space and special characters in your pictures name , excel only allow letters, numbers and underscore they automatically replace space and special character by underscore.
I did remove any spaces and/or special characters in my picture names and still received the "reference isn't valid" error. Do you have any suggestions on what else could cause this?
Hi Alan, thanks for the excellent sharing. I am facing an issue, that is the name with '-', example Chan-Too-Hoo, then it doesn't worked. also if we input an invalid name, the 'previous image' still remain / display. this is causing some confusion. appreciate your adv. Thanks once again
thankyou for this video where you have explained very well how to return a pic in a cell by using name manager. issue is that it is technically working, but not instead of returning the pic, it is showing grey borders..something changes, but it's not the picture
Hi Fakhruddin Is the host cell of sufficient size to display the image? That’s all I can think of. Usually if it isn’t working, one or more of the steps have been missed :) Stay well Alan
Hi Alan. Your video was really helpful but I have a question. I set mine up with over 1000 photos of students and the last 30 photos on my list won’t change. I assume my data list is short but I don’t know how to fix it! Any advice?
Hi Alan , thank you for the tips i found your video very helpful, however as I'm completely new to the excel and everything i know is pretty much from videos like yours. My problem is as for most of the tasks i am using query formulas , but now i just stuck where i have a column which i put into the cell picture of barcodes . For example here is a simple formula which i am using : =QUERY('MASTER FILE'!A2:J,"SELECT A ,B,J,D WHERE D ="""&D3&""" ") . So where is the column 'J' there are many barcodes which i would like somehow to populate with the other columns . The formula is working but no matter where i choose to put J , simply it does not show anything on that specific column. Is there any chance to modify or actually how to populate certain columns depending on the drop down menu as it is in the formula WHERE D column = D3 . For example i got a recruiting big master file end collecting all data . My idea is all pictures personal document photos to put them into the cell for future audit if need. I just want simply to populate what i got as a picture in any column to come along with the others. Thank you so much
Hi Alan! How cool is this feature!!! My question though, I have my "mugshot" cell referencing a =vlookup cell and when the data changes in the vlookup cell the mugshot cell doesn't automatically change pictures until I click on the picture. Is there a way to make it automatically change when the data in the vlookup cell changes? (vlookup cell references a state based on how many miles driven - if they type in 700 miles, Kansas will pop up, if they type in 2400 miles, California will pop up etc)
Hi Alan, Thanks a lot for the video.The explanation was crystal clear. I tried using this technique for my application, I keep getting an error "Reference isn't valid" when I try to re-name the photo cell with the Defined name. Could you please help? Thanks.
Hi Nikhil What name are you using (some are protected). Can you post a screen cap here...if not, can you post a link to a screen cap from dropbox or google drive? Alan
Hi Nikhil although its been 7 months since you ask this. i've got the same error before, but found a solution to it. try to clear the name manager and re-do Sir Alan's process. trust me, it will work
@@alanjarvis1892 There must me something magical happening. because when I get to (time index 3:53), typing mug, the image I have goes away and all is lost. thanks anyway Alan.
@@alanjarvis1892 Sorry - my fault. Lets say there where pictures of ears, nose, eyes, hair etc. and you instead of typing in a name chose among a dropdown of different set of pre-defined ears. By selecting a set of ears they appear in the cell - then next step is you select from a dropdown with different noses - and by selecting the nose appear in the same cell as the ears ( sort of on top on the "ears-picture") and the next and the next until a full face have been "created" by layers(pictures) of selections done by the user. A sort of a "Built a face" configuration. Hope this made more sense ?:-)
Hi Alan, I'm having a hard time even watched 4 videos on the same thing done differently, i still can not manage to get what i am doing right. i am trying to use a product code to pull up a barcode but it just does not want to work.
Is there a way to automate this for a whole column of cells? I have a list of pupils, their percentile rank, and I want to insert a picture relating to their rank in the column next to it. I have 300+ pupils in the list, and they are ranked on 7 different tasks so doing this for each one would take ages!
Hi Allan...really considering doing your course !...but I have a question ....My goal is to create an excel spreadsheet that will work as a recipe system for me to track my ingredient list and the costings to see in the end how it affects my final product cost plus be able to change different ingredient costs as they may occur and it automaticly adjust my end cost price.Will your course cover these kinds of applications of excel?
Hi Sherlock, Yes, that's all covered in Level 2. Try it out on Skillshare, just follow the subscribe links on my website. You can always cancel if you're not happy :) Stay well Alan
Hi Andreea, Yes, as long as the flag or chart is a "picture" (jpeg or similar), and is completely contained within the cell border, you can use this for anything! Have a great day! Alan
@@alanjarvis1892 I mean those which are imported from the native location. By using your method (which is great by the way and thanks for this!) it's showing the picture of cell instead of pasting only the picture located in indirect cell. So it's a great solution if you're looking at the data in Excel only but not that useful if you need to copy and paste the reference pictures to PowerPoint.
@@jaqbas93 Ah, it's just s lookup for use in Excel, not to integrate with PowerPoint. Though that is covered in Level 3 of this series. It's worth checking out on SkillShare :)
Has anyone had issues with the pictures/picture of a cell being cropped strangely or shifting in the place you are using the indirect reference? For example, when you search Ashley here, maybe it only shows half her face and she's right justified instead of centered?
Hi Alan, thanks for this :) Do you know if there is a way to display an image in each row of a table based on a criteria? Say for example a table with a bunch of values, but in row B2 is the name of the person and in A2 have the picture of them? So that each time I add a new row, their picture appears when I type their name. In other words, a method of not using a pulldown and having multiple images instead of just 1 displaying at any given time? In my worksheet, I require around 20 images which need to remain on display at the same time independently, based on their respective criteria in column B. The list is currently 300 rows long, fortunately the images sizes are negligible :)
Hi Rob Sure, just create a separate named range for the mugshot cell (B2 in the lecture) down as many rows as you want, so you may have Mugshot1, Mugshot2, Mugshot3 etc in A1, A2, A3. Then point your corresponding "picture" cell to those. A pain to set up, but you only need set it up once, and it's done forever :) Alan
For name Asley I put vlookup function to call the name. In that case when I replace a reference of a picture to MugShot it gives an error "Reference isn't valid". Please help.
OMG...... All I want to do is put a word into a cell and in another cell a pic populate the cell. That is all that needs to happen but ooohhhhh no. I have been working at getting exactly what I just wrote for the past 4 hours...... I have gotten one cell with a drop down list to produce the pic in the other cell but I can not for the life of me figure out how to copy that drop down list to other cells and have those selections put in other pics. Having 1 cell only that can put 1 pic from a list of pics into 1 cell is worthless to me. I have 215 cells that have drop downs and those drop downs need to each produce a pic in the cell to the left of it and not have all the pics be the same. Excel can get aggrivating sometimes and this one is off the charts.
Yesssss.. this is the most simplified i've ever seen so far... thankssss a lot Alan, you're so amazing.... more power to you thankssssss....
Glad you liked it!
How to put v lookup from folder to excel
Hello guys , for those having a "reference isn't Valid" error , try removing(or replace by underscore) the space and special characters in your pictures name , excel only allow letters, numbers and underscore they automatically replace space and special character by underscore.
yeah man! it worked!, thanks both, Alan and JoLo
I did remove any spaces and/or special characters in my picture names and still received the "reference isn't valid" error. Do you have any suggestions on what else could cause this?
Hi Alan, thanks for the excellent sharing. I am facing an issue, that is the name with '-', example Chan-Too-Hoo, then it doesn't worked. also if we input an invalid name, the 'previous image' still remain / display. this is causing some confusion. appreciate your adv. Thanks once again
THANKS SIR ,LEARN 100% FROM U
thankyou for this video where you have explained very well how to return a pic in a cell by using name manager. issue is that it is technically working, but not instead of returning the pic, it is showing grey borders..something changes, but it's not the picture
Hi Fakhruddin
Is the host cell of sufficient size to display the image? That’s all I can think of. Usually if it isn’t working, one or more of the steps have been missed :)
Stay well
Alan
Hi Alan. Your video was really helpful but I have a question. I set mine up with over 1000 photos of students and the last 30 photos on my list won’t change. I assume my data list is short but I don’t know how to fix it! Any advice?
Hi Alan , thank you for the tips i found your video very helpful, however as I'm completely new to the excel and everything i know is pretty much from videos like yours. My problem is as for most of the tasks i am using query formulas , but now i just stuck where i have a column which i put into the cell picture of barcodes . For example here is a simple formula which i am using : =QUERY('MASTER FILE'!A2:J,"SELECT A ,B,J,D WHERE D ="""&D3&""" ") .
So where is the column 'J' there are many barcodes which i would like somehow to populate with the other columns . The formula is working but no matter where i choose to put J , simply it does not show anything on that specific column. Is there any chance to modify or actually how to populate certain columns depending on the drop down menu as it is in the formula WHERE D column = D3 . For example i got a recruiting big master file end collecting all data . My idea is all pictures personal document photos to put them into the cell for future audit if need. I just want simply to populate what i got as a picture in any column to come along with the others. Thank you so much
Hi Alan! How cool is this feature!!! My question though, I have my "mugshot" cell referencing a =vlookup cell and when the data changes in the vlookup cell the mugshot cell doesn't automatically change pictures until I click on the picture. Is there a way to make it automatically change when the data in the vlookup cell changes? (vlookup cell references a state based on how many miles driven - if they type in 700 miles, Kansas will pop up, if they type in 2400 miles, California will pop up etc)
Hi Alan, Thanks a lot for the video.The explanation was crystal clear. I tried using this technique for my application, I keep getting an error "Reference isn't valid" when I try to re-name the photo cell with the Defined name. Could you please help? Thanks.
Hi Nikhil
What name are you using (some are protected). Can you post a screen cap here...if not, can you post a link to a screen cap from dropbox or google drive?
Alan
Hi Nikhil
although its been 7 months since you ask this.
i've got the same error before, but found a solution to it.
try to clear the name manager and re-do Sir Alan's process. trust me, it will work
@@alanjarvis1892 Hi sir, thanks for this process. found this helpful in my work
@@erwincervantes2860 My pleasure!
Hello. great tutorial. everything worked right up to the end where I typed the name in that selected cell but the image disappeared.
Thanks! That can only mean you did something differently
@@alanjarvis1892 There must me something magical happening. because when I get to (time index 3:53), typing mug, the image I have goes away and all is lost. thanks anyway Alan.
(still learning) I need help duplicating the information in another row. When I do, it changes all the images at same time.
Thank you. Very helpful!
Happy it helped 👍
Hi Alan - Great video 👍👍 Would be possible to "built" upon the picture with further "pictures" into an all ready chosen picture ?
I don’t know what that means?
@@alanjarvis1892 Sorry - my fault. Lets say there where pictures of ears, nose, eyes, hair etc. and you instead of typing in a name chose among a dropdown of different set of pre-defined ears. By selecting a set of ears they appear in the cell - then next step is you select from a dropdown with different noses - and by selecting the nose appear in the same cell as the ears ( sort of on top on the "ears-picture") and the next and the next until a full face have been "created" by layers(pictures) of selections done by the user. A sort of a "Built a face" configuration. Hope this made more sense ?:-)
Hi Alan, I'm having a hard time even watched 4 videos on the same thing done differently, i still can not manage to get what i am doing right. i am trying to use a product code to pull up a barcode but it just does not want to work.
for people having ref errors, it appears it doesn't work with spaces in words
Is there a way to automate this for a whole column of cells? I have a list of pupils, their percentile rank, and I want to insert a picture relating to their rank in the column next to it. I have 300+ pupils in the list, and they are ranked on 7 different tasks so doing this for each one would take ages!
Hi, Alan, what would be the v look up for displaying a picture after a badge is scanned?
The same? It doesn't matter hat the picture is :)
May i know what excel version you use ? Tq
All of them?
@@alanjarvis1892 no.. just this one... 365 office?
Hi Allan...really considering doing your course !...but I have a question ....My goal is to create an excel spreadsheet that will work as a recipe system for me to track my ingredient list and the costings to see in the end how it affects my final product cost plus be able to change different ingredient costs as they may occur and it automaticly adjust my end cost price.Will your course cover these kinds of applications of excel?
Hi Sherlock,
Yes, that's all covered in Level 2. Try it out on Skillshare, just follow the subscribe links on my website. You can always cancel if you're not happy :)
Stay well
Alan
Can we use the same technique to have flags (pictures) as chart labels?
Hi Andreea,
Yes, as long as the flag or chart is a "picture" (jpeg or similar), and is completely contained within the cell border, you can use this for anything!
Have a great day!
Alan
Is there an option to paste only concerned pictures instead of pictures of cells as you've mentioned?
What does concerned pictures mean?
@@alanjarvis1892 I mean those which are imported from the native location. By using your method (which is great by the way and thanks for this!) it's showing the picture of cell instead of pasting only the picture located in indirect cell. So it's a great solution if you're looking at the data in Excel only but not that useful if you need to copy and paste the reference pictures to PowerPoint.
@@jaqbas93 Ah, it's just s lookup for use in Excel, not to integrate with PowerPoint. Though that is covered in Level 3 of this series. It's worth checking out on SkillShare :)
Hi Alan, why is it the image didn't appear in me using the MugShot?
Thank you
Has anyone had issues with the pictures/picture of a cell being cropped strangely or shifting in the place you are using the indirect reference? For example, when you search Ashley here, maybe it only shows half her face and she's right justified instead of centered?
Hi Alan, thanks for this :)
Do you know if there is a way to display an image in each row of a table based on a criteria?
Say for example a table with a bunch of values, but in row B2 is the name of the person and in A2 have the picture of them? So that each time I add a new row, their picture appears when I type their name.
In other words, a method of not using a pulldown and having multiple images instead of just 1 displaying at any given time?
In my worksheet, I require around 20 images which need to remain on display at the same time independently, based on their respective criteria in column B.
The list is currently 300 rows long, fortunately the images sizes are negligible :)
Hi Rob
Sure, just create a separate named range for the mugshot cell (B2 in the lecture) down as many rows as you want, so you may have Mugshot1, Mugshot2, Mugshot3 etc in A1, A2, A3. Then point your corresponding "picture" cell to those. A pain to set up, but you only need set it up once, and it's done forever :)
Alan
For name Asley I put vlookup function to call the name. In that case when I replace a reference of a picture to MugShot it gives an error "Reference isn't valid". Please help.
Hi Heybat
IT's Ashley, not Asley. Just follow the video step by step, and it will work for you!
Stay well
Alan
I have the same problem and it doesn't even work without the vlookup
Hi, in my computer the excel returns "Reference isn't valid" when I change the formula to the name I create
What name are you using?
@@alanjarvis1892 I'm using "parts", now it's working
It is possible for single cell. But it is not possible for many rows in a table... Is there any other method.. ?
What do you mean by "not possible for many rows in a table"?
@@alanjarvis1892 Hi Alan, is there any easy way to apply this to for an example 300 cells ?
I applied this technique for the table of 800 rows and it worked
@@babywalk2272 how did you apply this to the rows underneath?
@@alanjarvis1892 how do you apply this all of the rows in your sheet? mine copies the picture but then doesnt change with the new cell value
Okay while brilliant where did you use vlookup.
Still I can use this. This might be the easiest solution ive found
Cool!
can i send you the sheet via email and you can see what is wrong with my formula please
This is a free video, for consultancy you can contact me via the website.
Thanks
Alan
OMG...... All I want to do is put a word into a cell and in another cell a pic populate the cell. That is all that needs to happen but ooohhhhh no. I have been working at getting exactly what I just wrote for the past 4 hours...... I have gotten one cell with a drop down list to produce the pic in the other cell but I can not for the life of me figure out how to copy that drop down list to other cells and have those selections put in other pics. Having 1 cell only that can put 1 pic from a list of pics into 1 cell is worthless to me. I have 215 cells that have drop downs and those drop downs need to each produce a pic in the cell to the left of it and not have all the pics be the same. Excel can get aggrivating sometimes and this one is off the charts.
I just met with reference isn't valid.
Same problem hmmm