2:46 You can create a name for column stage (eg, _stage = Table1[Stage]) and then refer to the name in Data Validation (=_stage). The range will be automatically updated since you name a dynamic area.
Yes, but you only need to do this extra step if you expect to add more stages to the table. In this example that wouldn't happen. BTW, if the Table is on the same sheet as the data validation list, you also don't need the name. Just referencing the cells in the table using their column row references e.g. $C$2:$C$22 will result in a dynamic reference too.
Hi Mynda. It's amazing the possibility that excel can do. I always watch your tutorial video. I will definitely use this one in the future. Thanks for sharing. Good Stuff. 🙂
Tips. 1. Pay attention to the formulas, they are adding brackets and commas by typing them manually in places 2. If you are getting problems with =profile_dv stating reference isn't valid go back and check your brackets and commas OR check that your cells containing images are named correctly (i.e. that they are all named Image_1, Image_2, etc instead of A1, etc), if there isn't consistency you will get that error. (Thanks for the guide @myonlinetraininghub)
Great video! I wondered if you had suggestions that could take picture lookup just a step further? I am looking to create a document that has images of employees that work throughout a week. I am still quite new to power query, but I was hoping to use picture lookup with slicers and pivot table to select the day of the week and unselect employees working on that specific day. My thoughts are that I would need to create slicer connections between the different days but I'm trying to figure out how I can get picture look up to show multiple images from the slicers items that have been selected. Any thoughts on how this could be done?
Hello, I followed the video, but when I put the named range in place of image it gives me error 'Reference not valid'. Tried doing it again by following the steps you mentioned carefully but still cant get it right. Any suggestions what could be going wrong? I am using Excel 2010
Great to hear you're giving it a go. I'd say there is an error in the formula somewhere but it's difficult to troubleshoot without seeing it. Please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum
Great video! Thank you so much. Question: if you have a series of charts and you want to pull in those charts based on a cell value, can you use Excel's Camera feature to take images of each chart and use those ?
Hi Douglas, even better, just put the charts inside the cell and use the technique described here. That way you don't need the camera tool step and the associated distortion of the image you get with the camera.
@@MyOnlineTrainingHub Yes! That makes perfect sense. I will do that. Thank you very much for all your top quality Excel training videos and web site. You always provide great tips and techniques. Very much appreciated!
Glad you liked it, Ramesh. Hard to say what's causing the blank image, but it'll be something in the way you've set it up. Probably your INDIRECT formula.
Yes, you can filter a table with a Slicer, but then you have to figure out which row is visible, which can be done with formulas, but I just thought it was easier to use a PivotTable :-)
Great! I stumbled upon your video while watching bike videos ;-) I will definitely use that for my next roadbike trips, and for sure, in other professional circomstances! Thanks Linda
Thanks for the tutorial. I am working on a Macbook Pro. Any idea why the images inserted (with lookup) are not the same size as the images on the images tab? "size" is actually the same, but only see the upper left corner of the image. this is both on my local file and yours that I downloaded.
I'd have thought it was because the source image isn't in a single cell, but if it's happening in my file as well as yours then I'm not sure why that would be, sorry.
@@MyOnlineTrainingHub thanks so much for your reply. It must have something to do with image size and resolution. I tried another approach/technique and same result.
Thanks!!! This video is very useful. Is it possible to view this excel file on Google sheet? The drop down list and images seem not working after uploading the file to Google Drive/Sheet. Can you advise?
Congrats, perfect. Is it possible with charts instead of images ? I wouldn’t like to convert a chart in a image so I can have a live graphic. Thanks a lot
Yes, most you can definitely use this for charts too. Just put them is a big cell :-) That said, I've found they don't render perfectly, but I haven't tried in in the newer builds of Excel.
@@MyOnlineTrainingHub- I'm using Office 365 and when a try doing the last pass creating a '=profile_dv' reference for image Excel says 'Reference isn't valid' ;-(( ... any idea what i'm missing ? thanks in advance
Hi Mynda. I noticed as you created the pivot table for the Slicer, your Stage data was sorted correctly with no further changes. When attempting to produce the same, my data was sorted from stage 1 to 19 followed by 2 etc. Could you kindly advise if you had perhaps already created a custom order elsewhere? I can manually change the sort order of the pivot to align however when creating the slicer, I have the same issue. Stages are not in numeric order. Thank you.
Hi Dillan, You can use a Custom List to sort the Slicer. Excel File tab > Options > Advanced. Then in the Slicer settings ensure the 'use custom lists' is selected for sorting. Mynda
What effect does resizing the presentation level picture have? Could you start with very large images and resize smaller on the presentation sheet? Could be great for dashboards in the charts were placed in a single cell.
Great video, everything worked fine with the initial 2 x images i had set up. I went to add a third image the next day (and amend the formulas to suit) but as soon as I increased the row depth to add the third image below my 'linked image' started getting corrupted and moving all over the place within the reference frame. Is there a trick to make sure this doesnt happen? It appears to be okay if i add the new image above the previous images but just wondering if this is a known issue? Thanks in advance
I'm not aware of this issue. If an image overlaps a cell, this will cause issues, so if by resizing the row/column the image is now not completely inside a cell then you should resize the image to fit within the cell bounds.
With the slicer method (or with slicer use in general) is there a way to prevent the use of Multi-select (For instances like this where it wouldn't make sense to have multiple stages selected simultaneously)? I could remove the header which removes the multi-select button, but that doesn't prevent the use of ctrl+click to select multiple options.
What if I don't want to use a drop downlist but another list? I've got my list with pictures and in another tab I create a list of items from the first list. The pictures of the first list should appear alongside the items of the second list.Say I have a list of animals with corresponding pictures; monkee, deer, elephant, rhino, beaver, lion and I have second list with monkee and lion. How do I get the right pictures to show up in the second list?
Hello, I'm trying to do something similar to this, but there are some slight differences. It's not something I really want to spend a lot of time on. Is it possible to just hire you to do what I need?
Hi Evan, I don't do consulting, but you're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I'm getting this problem when i try to link the picture to the slicer, it comes up as "reference is invalid". I tried this with my own list from a different tutorial where i made my own data and got stuck on the same thing. There must be a setting somewhere that's not allowing me to link the indirect formula to that image, but i don't know where.....
The size of the shape that displays the image is fixed when you insert it. It doesn't know how big the cell is that it's referencing. You'd have to write some VBA code to automate this..
I followed the video, but when I put the named range in place of image it gives me error 'Reference not valid'. Tried doing it again by following the steps you mentioned carefully but still cant get it right. Any suggestions what could be going wrong? I am using Excel 365
Hi Emilia, I suspect there is a step missed or wrong name somewhere along the way. Double check everything and if you're still stuck, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for your excellent explanation. Sometimes, I don't know why, it does not work and shows the error message: "Reference Isn't valid." Why it happens? How to solve it?
Reference errors occur when the cell or name you're referring to does not exist or is incorrect. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
HI! I use this method to pull photographs and it works great but the photos end up having this sort of grey box or line on the left side and bottom of most of the images. Is there a way to remove this?
the linked images get very blurry, and I don't seem to be able to fix this. The original pictures are imported at the desired resolution. but when it's displayed where I want it to be displayed, the image is blurry. by the way: i want to use this function to change the logo, based on the department selected of the company. (each department has it's unique color) thx in advance!
For best results make sure the image and linked image are the same size, otherwise the image will get distorted/blurry when you bring it into the linked image.
HI, I was wondering is it possible to show an image without a drop-down menu? I have a league table where the teams move up and down depending on the results obtained, so would like the pic in a cell next to the cell with the name of the team. Any advice would be appreciated.
@@MyOnlineTrainingHub Hi Mindy, please excuse my ignorance but could you explain what value in what cell? I have tried to follow the same process but it does not compute with my brain.
Currently you select a value in a cell from a data validation list. Instead of selecting the value from the data validation list, just type the value in the cell. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I've tried this and it just won't work. The image stays stubbornly as the one I pasted in and doesn't change when I select items from the dropdown list. Maybe it's because I'm using Excel for Mac. It wouldn't surprise if this is yet another thing deemed not worthy of the Mac version of Excel by Microsoft, even though we pay the same subscription as Windows users.
There's nothing in this technique that wouldn't work on a Mac. Please post your question and sample Excel file on our forum where someone can help you figure out the problem: www.myonlinetraininghub.com/excel-forum
I eventually got it to work. What the video didn't seem to say (unless I missed it somewhere) is that you have to copy the CELL, not the image, on the reference sheet. You then get all the relevant paste options, including Linked Picture. Once I did this it worked instantly.
Hi Mynda, can I know why when I try to link the selected image to my name formula, there is an error showing reference isn't valid. I have follow exactly all the same steps but still cannot, please advise.
This usually means the defined name formula is incorrect. You can download my file and compare to yours here: www.myonlinetraininghub.com/lookup-pictures-in-excel
Thank you. Very informative and easy to follow. I have been able to use the XLOOKUP with the slicer. However when I post the workbook in Google Drive to share with the team; it does not show the slicer. Any suggestions?
When I try to change the value of my image cell to its defined list name (costumes in my case), although costumes is coming up as the only available list name, I'm getting Reference is not valid. Any ideas?
Hi Chris, please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Is there anyone that you would trust in recommend to make something similar to one of your impressive excel spreadsheets except from a Google sheet? Thank you
@@MyOnlineTrainingHub i mean for example to change A1to profile_1 and A2 to profile_2 and so on .in same example , if i have like hundreds of pictures , shall i change the cell name manually ?
It's so odd that Microsoft haven't added native support for this. I read a question in MS help on their website from 2003 asking for this. Almost 20 years ago. I've seen it asked for countless times and, while your video is one of the best (Along with Leila Gharani's explanation) , there are perhaps 30 videos on TH-cam explaining the arcane workarounds people need to use to get this done. That's Ok. I can do it. But by now, after so many people asking for it, for so many years - it really should have its own function in Excel by now.
Is it just only me ? problems 1, the excel keep on naming the picthure ranther than cell, even I selected the cell not the pic. problem 2, I put the pics in a table, the return picture always have a border.
The picture returns the border because you have borders on the cells. If you don't want to see the cell border, turn them off on the View tab of the ribbon.
When I try pull in my picture with the formula it gives the error "Reference isn't valid". What did I do wrong? I think I followed the steps correctly.
Hard to say without seeing the file, but I guarantee you missed something along the way. I'd retrace the steps and if you're still stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub You're wrong. When you use it in name namager, it slows down excel exponentially more than a regular cell. Especially macros. Macros become super slow. Just try 4 dynamic images, create 4 of them and try to run a macro. I also tried index and xlookup in the name manager to no avail. They all slow down excel when used for image lookup.
The exponential of a very tiny amount is still a tiny amount. I wouldn't use INDIRECT with VBA because there's invariably a non-VBA solution to what you're probably doing. My motto is to avoid VBA where possible.
@@MyOnlineTrainingHub You're likely wrong. I have 10 text strings in a1:a10 each having different font style, some are bold, some are italic. I use a VBA code to combine them into a paragraph in c10 with all the strings protecting their font styles and colors. There's no solution for this other than VBA.
Must be something wrong, but hard to say without seeing the file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
No. Go back through the steps and double check everything as there must be a mistake along the way. If you're still stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
i did this but it made excel act crazy. It also made the images significantly lower quality. Please help. Ive been working on a spreadsheet masterpiece for almost a year and this is the last thing i need to complete it.
Sounds like the images aren't the same size as the image placeholder. You're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I got as far as the x lookup and it presented #Name, instead of Profile_2 so I changed it from x to v lookup and it worked for all numbers over 20 those under 20 gave random profiles.
So i thought i would try the slice method, I set up the pivot table and it has the numbering out of sequence stage 1 at the top followed by 10 to 19 then 2 followed by 20 and so on. I added the slicer and it has the same issue. When i try to add formula see 5.54 the refers to box is greyed out so i cannot get any further. so both do not wish to work.
Sounds like you don't have a version of Excel with XLOOKUP, in which case you correctly used VLOOKUP. If your Slicer numbers are out of sequence it will be because they are being interpreted as text, not numbers. If you're still stuck, you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHubThank you... i got the slicer to work today, still the issue with the numbering , how do i fix that? When i select an image, is it possible to display other data in another cell for example a description?
Hi, I'm really glad to follow your channel. Appreciated your nice vedios. If you please to make a video for big tables comparing, and get table of differences. Sincerely,
It's great to know you're enjoying my videos :-) I haven't got a video on it yet, but I've written a tutorial on using Power Query to compare tables here: www.myonlinetraininghub.com/excel-compare-two-lists
@@MyOnlineTrainingHub thank you for sharing this tutorial, but I have faced a lot of errors because the tables have many columns. If you to take this topic in your considerations, and create new video covers all issues. To make my work easier. Sincerely,
Hi Bob, it's a complicated technique and if you don't name things correctly, you'll get errors. We're happy to help though. If you'd like to post your Excel file and question on our forum we can see what the problem is: www.myonlinetraininghub.com/excel-forum
Finally I got it.
After watching several other instructions and failing, you managed to point out the crucial issues.
Thanks, well done again.
So pleased it was helpful 😊
2:46 You can create a name for column stage (eg, _stage = Table1[Stage]) and then refer to the name in Data Validation (=_stage). The range will be automatically updated since you name a dynamic area.
Yes, but you only need to do this extra step if you expect to add more stages to the table. In this example that wouldn't happen. BTW, if the Table is on the same sheet as the data validation list, you also don't need the name. Just referencing the cells in the table using their column row references e.g. $C$2:$C$22 will result in a dynamic reference too.
Hi Mynda.
It's amazing the possibility that excel can do. I always watch your tutorial video.
I will definitely use this one in the future. Thanks for sharing. Good Stuff. 🙂
Great to hear, Jocelyn!
Tips. 1. Pay attention to the formulas, they are adding brackets and commas by typing them manually in places 2. If you are getting problems with =profile_dv stating reference isn't valid go back and check your brackets and commas OR check that your cells containing images are named correctly (i.e. that they are all named Image_1, Image_2, etc instead of A1, etc), if there isn't consistency you will get that error. (Thanks for the guide @myonlinetraininghub)
Thanks for documenting the gotchas. A lot of people get caught out by these intricacies!
Your solutions and techniques have helped me out alot, especially at work. thank you
Wow, that's great to know! Please share my videos and Excel love with your co-workers too. Mynda
That is great. Can we do more simple with new image() function?
Yes, but only if you save your images on the web as the IMAGE function requires a URL.
Superb, thank you for taking the time to deliver such a concise presentation.
Glad it was helpful!
Great video! I wondered if you had suggestions that could take picture lookup just a step further? I am looking to create a document that has images of employees that work throughout a week.
I am still quite new to power query, but I was hoping to use picture lookup with slicers and pivot table to select the day of the week and unselect employees working on that specific day.
My thoughts are that I would need to create slicer connections between the different days but I'm trying to figure out how I can get picture look up to show multiple images from the slicers items that have been selected. Any thoughts on how this could be done?
Power BI Custom Data Types might do what you want: www.myonlinetraininghub.com/power-bi-organizational-data-types-in-excel
This is awesome Mynda! Just found it. Thanks for sharing these two techniques :)) Thumbs up!!
Awesome! Thanks, Wayne!
Hello,
I followed the video, but when I put the named range in place of image it gives me error 'Reference not valid'. Tried doing it again by following the steps you mentioned carefully but still cant get it right. Any suggestions what could be going wrong? I am using Excel 2010
Great to hear you're giving it a go. I'd say there is an error in the formula somewhere but it's difficult to troubleshoot without seeing it. Please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum
Great video! Thank you so much. Question: if you have a series of charts and you want to pull in those charts based on a cell value, can you use Excel's Camera feature to take images of each chart and use those ?
Hi Douglas, even better, just put the charts inside the cell and use the technique described here. That way you don't need the camera tool step and the associated distortion of the image you get with the camera.
@@MyOnlineTrainingHub Yes! That makes perfect sense. I will do that. Thank you very much for all your top quality Excel training videos and web site. You always provide great tips and techniques. Very much appreciated!
Thanks for a great video! I'm getting a blank image returned when I use this method - any advice . Many thanks
Glad you liked it, Ramesh. Hard to say what's causing the blank image, but it'll be something in the way you've set it up. Probably your INDIRECT formula.
Very Smart Way to change picture. Note that segment can be used directly on the table, avoiding such a way the pivot.
Thanks V2P
Yes, you can filter a table with a Slicer, but then you have to figure out which row is visible, which can be done with formulas, but I just thought it was easier to use a PivotTable :-)
Wow!! I'am a passionate cyclist. I can imagine that this presentation was made with excel! Amazing!
Glad you enjoyed it :-)
Wow, I love this! Simple, easy, unique, effective! I prefer the slicers method. Thanks Linda 👌👍💯❤
You are so welcome!
Thank you i just discover your channel. I hope i can apply it. Just started learning Excel this month.
Welcome! There's a Excel for beginner's playlist here: th-cam.com/video/MnGIaqpqLTU/w-d-xo.html
always great Mynda
Thanks, Hazem!
Mynda, how do you do this for each row in an evergrowing table?
not sure which table is growing, but I imagine manually as each picture requires a unique name etc.
Awesome. Thank you Mynda
You are so welcome!
Great! I stumbled upon your video while watching bike videos ;-) I will definitely use that for my next roadbike trips, and for sure, in other professional circomstances! Thanks Linda
:-D glad you'll be able to make use of it!
Thanks for the tutorial. I am working on a Macbook Pro. Any idea why the images inserted (with lookup) are not the same size as the images on the images tab? "size" is actually the same, but only see the upper left corner of the image. this is both on my local file and yours that I downloaded.
I'd have thought it was because the source image isn't in a single cell, but if it's happening in my file as well as yours then I'm not sure why that would be, sorry.
@@MyOnlineTrainingHub thanks so much for your reply. It must have something to do with image size and resolution. I tried another approach/technique and same result.
Thanks!!! This video is very useful. Is it possible to view this excel file on Google sheet? The drop down list and images seem not working after uploading the file to Google Drive/Sheet. Can you advise?
I can't speak for Sheets, sorry.
Congrats, perfect. Is it possible with charts instead of images ? I wouldn’t like to convert a chart in a image so I can have a live graphic. Thanks a lot
Yes, most you can definitely use this for charts too. Just put them is a big cell :-) That said, I've found they don't render perfectly, but I haven't tried in in the newer builds of Excel.
MyOnlineTrainingHub thanks, i’ll try in office 365 and give you a feedback later
@@MyOnlineTrainingHub- I'm using Office 365 and when a try doing the last pass creating a '=profile_dv' reference for image Excel says 'Reference isn't valid' ;-(( ... any idea what i'm missing ? thanks in advance
curiosly, i downloaded your sample worksheet and everything works 100% .... ;- ((
Hi Mynda. I noticed as you created the pivot table for the Slicer, your Stage data was sorted correctly with no further changes. When attempting to produce the same, my data was sorted from stage 1 to 19 followed by 2 etc. Could you kindly advise if you had perhaps already created a custom order elsewhere? I can manually change the sort order of the pivot to align however when creating the slicer, I have the same issue. Stages are not in numeric order. Thank you.
Hi Dillan, You can use a Custom List to sort the Slicer. Excel File tab > Options > Advanced. Then in the Slicer settings ensure the 'use custom lists' is selected for sorting. Mynda
@@MyOnlineTrainingHub Thank you!
What effect does resizing the presentation level picture have? Could you start with very large images and resize smaller on the presentation sheet? Could be great for dashboards in the charts were placed in a single cell.
It's best if the original image is the size you want it to be in the Dashboard, otherwise it can look blurry. Definitely useful for charts :-)
Great video, everything worked fine with the initial 2 x images i had set up. I went to add a third image the next day (and amend the formulas to suit) but as soon as I increased the row depth to add the third image below my 'linked image' started getting corrupted and moving all over the place within the reference frame. Is there a trick to make sure this doesnt happen? It appears to be okay if i add the new image above the previous images but just wondering if this is a known issue? Thanks in advance
I'm not aware of this issue. If an image overlaps a cell, this will cause issues, so if by resizing the row/column the image is now not completely inside a cell then you should resize the image to fit within the cell bounds.
Can you link it to charts instead of pics? So users can use slicer to select a particular chart instead of pics?
Yes, absolutely.
Nice and so Easy!I'd this requirements years ago, but i was never able to do this.Thanks again for sharing your knowledge and in a very friendly way
You're most welcome, Manuel :-) Please share it with your co-workers too. Mynda
With the slicer method (or with slicer use in general) is there a way to prevent the use of Multi-select (For instances like this where it wouldn't make sense to have multiple stages selected simultaneously)? I could remove the header which removes the multi-select button, but that doesn't prevent the use of ctrl+click to select multiple options.
That's a great idea, but unfortunately, there's no way to prevent multi-select with Excel Slicers :-(
@@MyOnlineTrainingHub Bummer, OK. Guess I'll just have to rely on Users being smart/nice. Thanks!
Again... amazing!!! Thank you so much!!!
You are most welcome! 😊
Very clever! Thanks, Mynda!
Thanks, Jim :-)
What if I don't want to use a drop downlist but another list? I've got my list with pictures and in another tab I create a list of items from the first list. The pictures of the first list should appear alongside the items of the second list.Say I have a list of animals with corresponding pictures; monkee, deer, elephant, rhino, beaver, lion and I have second list with monkee and lion. How do I get the right pictures to show up in the second list?
You can use the same technique, but instead of one image placeholder, you'll need one for each animal listed.
I have a question, how can i dynamically select 3 or more value in the slicer and return the 3 or more pictures at 1 go?
Not sure this is possible, Jonathan.
Have used this a year ago..always a winner!
Great to hear :-)
Hello, I'm trying to do something similar to this, but there are some slight differences. It's not something I really want to spend a lot of time on. Is it possible to just hire you to do what I need?
Hi Evan, I don't do consulting, but you're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How does the display behave when one tries to select multiple stages with the slicers
Hi Bright, only the first selection is displayed.
@@MyOnlineTrainingHub OK. I get it now. Thanks!
I'm getting this problem when i try to link the picture to the slicer, it comes up as "reference is invalid". I tried this with my own list from a different tutorial where i made my own data and got stuck on the same thing. There must be a setting somewhere that's not allowing me to link the indirect formula to that image, but i don't know where.....
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Is there a way to have the photo automatically resize on the Lookup Images to be the same size as the cell the formula is in?
The size of the shape that displays the image is fixed when you insert it. It doesn't know how big the cell is that it's referencing. You'd have to write some VBA code to automate this..
Brilliant stuff. Did it loooooong ago but the one here much easier. Thanks.
Cheers, Chris!
I followed the video, but when I put the named range in place of image it gives me error 'Reference not valid'. Tried doing it again by following the steps you mentioned carefully but still cant get it right. Any suggestions what could be going wrong? I am using Excel 365
Hi Emilia, I suspect there is a step missed or wrong name somewhere along the way. Double check everything and if you're still stuck, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for your excellent explanation. Sometimes, I don't know why, it does not work and shows the error message: "Reference Isn't valid."
Why it happens?
How to solve it?
Reference errors occur when the cell or name you're referring to does not exist or is incorrect. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
HI! I use this method to pull photographs and it works great but the photos end up having this sort of grey box or line on the left side and bottom of most of the images. Is there a way to remove this?
It might be the cell borders. Have you tried turning off gridlines?
the linked images get very blurry, and I don't seem to be able to fix this.
The original pictures are imported at the desired resolution. but when it's displayed where I want it to be displayed, the image is blurry.
by the way: i want to use this function to change the logo, based on the department selected of the company. (each department has it's unique color)
thx in advance!
For best results make sure the image and linked image are the same size, otherwise the image will get distorted/blurry when you bring it into the linked image.
So clever! Would this work with graphs that are updated regularly or do they need to be pictures?
It sure will work with charts. Any object you put in a cell will work.
@@MyOnlineTrainingHub Thanks, I think this could be really useful for me. Love the channel - you explain everything so well x
Very nice video.
Your video help me so much.
I very like your video.😍😍😍
So pleased to hear that!
HI, I was wondering is it possible to show an image without a drop-down menu? I have a league table where the teams move up and down depending on the results obtained, so would like the pic in a cell next to the cell with the name of the team. Any advice would be appreciated.
Sure, just enter the value in the cell instead of entering it via a drop down menu.
@@MyOnlineTrainingHub Hi Mindy, please excuse my ignorance but could you explain what value in what cell? I have tried to follow the same process but it does not compute with my brain.
Currently you select a value in a cell from a data validation list. Instead of selecting the value from the data validation list, just type the value in the cell. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I've tried this and it just won't work. The image stays stubbornly as the one I pasted in and doesn't change when I select items from the dropdown list. Maybe it's because I'm using Excel for Mac. It wouldn't surprise if this is yet another thing deemed not worthy of the Mac version of Excel by Microsoft, even though we pay the same subscription as Windows users.
There's nothing in this technique that wouldn't work on a Mac. Please post your question and sample Excel file on our forum where someone can help you figure out the problem: www.myonlinetraininghub.com/excel-forum
I eventually got it to work. What the video didn't seem to say (unless I missed it somewhere) is that you have to copy the CELL, not the image, on the reference sheet. You then get all the relevant paste options, including Linked Picture. Once I did this it worked instantly.
That is what I have been looking for. Many thanks for your efforts and support. It is awesome 👌✌️👌✌️
My pleasure :-)
Really Helpful And Clever Tutorial Thank You Mynda :):):)
Thanks, Darryl! Glad you liked it :-)
Hi Mynda, can I know why when I try to link the selected image to my name formula, there is an error showing reference isn't valid. I have follow exactly all the same steps but still cannot, please advise.
This usually means the defined name formula is incorrect. You can download my file and compare to yours here: www.myonlinetraininghub.com/lookup-pictures-in-excel
Thank you. Very informative and easy to follow. I have been able to use the XLOOKUP with the slicer. However when I post the workbook in Google Drive to share with the team; it does not show the slicer. Any suggestions?
Sounds like people might be opening the file in Google Sheets instead of Excel. I can’t speak for how Sheets might handle Excel features.
@@MyOnlineTrainingHub Understood. Yes, they are opening through Google sheets. Any suggestions on potential options to try other than with slicer?
No, I'm not familiar with Sheets.
You are amazing.Can you show us the mountainous image developing?
Thank you! I didn't create the maps themselves. They came from the Tour de France website.
Amazing, thanks Mynda!
Cheers, Luciano :-)
When I try to change the value of my image cell to its defined list name (costumes in my case), although costumes is coming up as the only available list name, I'm getting Reference is not valid. Any ideas?
Hi Chris, please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Gold, thanks.
Hi Mynda, how can it be done to use pics/ artwork/ symbols in a pie chart? JN
Hi Josef, you can set the fill for the pie chart series to an image in the color settings.
@@MyOnlineTrainingHub wow... Thanks for your reply!
Im really lost... How do one do that? Maybe I need to play around with it more
Thank you for such a nice tips!
Glad it was helpful!
Mam, can we add multiple pictures in one click. Like, in stage-1 can there be 5-6 pictures? Thank you!
Yes, if you put the pictures inside a single cell.
@@MyOnlineTrainingHub Thank you so much for your kind reply. You are amazing.
Do you make google sheets for customers? Thanks
No, sorry, Alex.
@@MyOnlineTrainingHub Is there anyone that you would trust in recommend to make something similar to one of your impressive excel spreadsheets except from a Google sheet? Thank you
what if we have plenty of pictures , how to change the cells names in sequence?
Hi Zaki, I'm not sure what you mean by 'in sequence'?
@@MyOnlineTrainingHub i mean for example to change A1to profile_1 and A2 to profile_2 and so on .in same example , if i have like hundreds of pictures , shall i change the cell name manually ?
Yes, manually. Unless you can write VBA code, in which case you may be able to do it programatically.
Top class as usual 👍
Aw, you're too kind, Brian :-)
Thanks for the video!
My pleasure, Doug! :-)
It's so odd that Microsoft haven't added native support for this. I read a question in MS help on their website from 2003 asking for this. Almost 20 years ago. I've seen it asked for countless times and, while your video is one of the best (Along with Leila Gharani's explanation) , there are perhaps 30 videos on TH-cam explaining the arcane workarounds people need to use to get this done.
That's Ok. I can do it. But by now, after so many people asking for it, for so many years - it really should have its own function in Excel by now.
I hear you. Hopefully it won't be for much longer.
Is it just only me ? problems 1, the excel keep on naming the picthure ranther than cell, even I selected the cell not the pic. problem 2, I put the pics in a table, the return picture always have a border.
The picture returns the border because you have borders on the cells. If you don't want to see the cell border, turn them off on the View tab of the ribbon.
When I try pull in my picture with the formula it gives the error "Reference isn't valid". What did I do wrong? I think I followed the steps correctly.
Hard to say without seeing the file, but I guarantee you missed something along the way. I'd retrace the steps and if you're still stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks a lot, but i have a problem
When equal the picture with (=profile_dv),
A message appears that " Reference isn't valid"
Thanks for your support
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Simple and very useful, thanks
Great to know you can make use of it, Weldy :-)
It would be more interesting if we could do it with graphics too but it was amazing
Congratulations
Hi Jose, you can do this with charts too. Just put the chart inside a cell, like I did with an image :-)
Muy buena explicación. Gracias !!!
Cool, thank you Mynda!
My pleasure :-)
This is volatile. It slows down excel and especially macros. Is there a non-volatile way??
INDIRECT only slows down Excel in a noticeable way if you use loads of these formulas. Using a few won't make any difference.
@@MyOnlineTrainingHub You're wrong. When you use it in name namager, it slows down excel exponentially more than a regular cell. Especially macros. Macros become super slow.
Just try 4 dynamic images, create 4 of them and try to run a macro.
I also tried index and xlookup in the name manager to no avail. They all slow down excel when used for image lookup.
The exponential of a very tiny amount is still a tiny amount. I wouldn't use INDIRECT with VBA because there's invariably a non-VBA solution to what you're probably doing. My motto is to avoid VBA where possible.
@@MyOnlineTrainingHub You're likely wrong. I have 10 text strings in a1:a10
each having different font style, some are bold, some are italic.
I use a VBA code to combine them into a paragraph in c10 with all the strings protecting their font styles and colors.
There's no solution for this other than VBA.
Great technique. Thank you for the tip.
My pleasure, Nelson :-)
Nice Mynda
Thanks, Lalit :-)
THIS IS AWESOME!
Glad you like it! 🙏
I keep getting reference isn’t valid. Tried it several times with same result.
Must be something wrong, but hard to say without seeing the file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Neat trick! I'm about to have an application for that trick. Thanks a lot.
Thanks, Frank! Glad you can use it :-)
Like this tutorial. Thank you.
anyone, i get error reference isnt valid
No. Go back through the steps and double check everything as there must be a mistake along the way. If you're still stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
very clever. thanks for sharing :)
You are so welcome!
Loved the video! And i'm super excited I have XLOOKUP now! Time to revisit your little tutorial!
Yay, XLOOKUP has finally landed :-) Share the news and my video with your co-workers. It's an amazing function. Mynda
Awesome video
Thanks
Thanks so much! :-)
i did this but it made excel act crazy. It also made the images significantly lower quality. Please help. Ive been working on a spreadsheet masterpiece for almost a year and this is the last thing i need to complete it.
Sounds like the images aren't the same size as the image placeholder. You're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks my savior!
Glad I could help!
EXCELlent technique
Thanks, Emre :-)
Thanks for sharing this..❤️
You're welcome, Sandip!
I got as far as the x lookup and it presented #Name, instead of Profile_2 so I changed it from x to v lookup and it worked for all numbers over 20 those under 20 gave random profiles.
So i thought i would try the slice method, I set up the pivot table and it has the numbering out of sequence stage 1 at the top followed by 10 to 19 then 2 followed by 20 and so on. I added the slicer and it has the same issue. When i try to add formula see 5.54 the refers to box is greyed out so i cannot get any further. so both do not wish to work.
Sounds like you don't have a version of Excel with XLOOKUP, in which case you correctly used VLOOKUP. If your Slicer numbers are out of sequence it will be because they are being interpreted as text, not numbers. If you're still stuck, you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHubThank you...
i got the slicer to work today, still the issue with the numbering , how do i fix that?
When i select an image, is it possible to display other data in another cell for example a description?
I keep getting a reference error
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, I'm really glad to follow your channel. Appreciated your nice vedios.
If you please to make a video for big tables comparing, and get table of differences.
Sincerely,
It's great to know you're enjoying my videos :-) I haven't got a video on it yet, but I've written a tutorial on using Power Query to compare tables here: www.myonlinetraininghub.com/excel-compare-two-lists
@@MyOnlineTrainingHub thank you for sharing this tutorial, but I have faced a lot of errors because the tables have many columns.
If you to take this topic in your considerations, and create new video covers all issues.
To make my work easier.
Sincerely,
Lovely! Thanks so much!
Cheers, Erica :-)
Wow, excellent. Would be very helpful in creating image based lessons (e.g. maps) for my kid.
Glad you'll be able to make use of it, Ajit :-)
Excelente! Muito bacana!!
Obrigado, Marcelo!
Gold, subbed with thanks.
Awesome, thank you!
absolutely it is great. thankyou so much.
You're most welcome, Faysal :-)
Thanks. very Very Helpful. :) :)
Great to know you can make use of it, John :-)
Excellent
nice!! =D congratulations
Cheers, Henry!
Amazing !
Cheers, Daniel! :-)
Amazing
Thank you!
thanks
You're welcome!
if u were a scientist I would have learned rocket science too
:-D so pleased you found this video helpful, Ishwar!
Terrible video. There were minor steps that seemed to be missed. I get reference not valid.
Hi Bob, it's a complicated technique and if you don't name things correctly, you'll get errors. We're happy to help though. If you'd like to post your Excel file and question on our forum we can see what the problem is: www.myonlinetraininghub.com/excel-forum