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.
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?
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
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
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
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?
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.
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.
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..
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
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
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
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?
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,
@@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 ?
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
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
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 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?
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
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
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.
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.
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
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!
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.....
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
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 RU-vid 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.
@@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 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.
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?
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.
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
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 :-)
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
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.
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.
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
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?
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)
@@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
