Dynamic Scatter Plot Chart Picture

[RuKeBo]

Not sure if this is specifically a "chart" issue but it is the chart part that is causing me issues.

I have an application where I need to have an image of something where the number of the items in the image will change from 1 to 5. I made 5 images, each with a different number of objects. The first image would have one object, the second two, the third three, and so on. I change the picture image to the one I need based on a cell value. If the cell is say "one" then it displays the image with one unit, if "two" then the image with two units, etc. If I simply use a Picture for this with the images in cells and then use the Named Manager to give them names, I can reference them by using INDIRECT and the cell where the name of the image it. This works fine and there are several guides online how to do this.

What I want to add to this is to locate some textboxes on each item in the picture to display some number. I can also get this to work using VB to locate the textboxes and hide or show them as needed. If there is only one unit in the image, I don't want all five textboxes shown, just the one. Because of the images and things being in different locations, I also need to re-position the textboxes, and do this in VB. The problem is that if the Picture changes size by any amount, or something else changes with it, everything is off. Also, trying to find the location of the textboxes relative to the entire sheet, and these are on page 4 of 5 I think, is cumbersome and relative to the whole document, not relative to the page or something on the page.

What I decided to do, was use a scatter plot chart with a point for every textbox I needed, then position the point where I needed it. I would use a Picture inserted in the chart to display the image, now everything seems to stick together and is relative to the Chart. The problem is the INDIRECT function is giving me an error "This formula is missing a range reference or a defined name". Now the exact way I am doing this works fine when the Picture is outside a Chart, except the other issues with the textbox locations. There is a Named reference for each image (ex. Image_1_1, Image_1_2, Image 2_1, etc.)). There is another Named reference for the formula of the image name which is =INDIRECT(cell_location_of_image_name) (Ex. Image_Name). What does work in the Chart is if I reference the image name directly. With the example names I gave using =Image_1_1 will display that image in the Picture. However, using either the Named reference (In this example =Image_Name) or =INDIRECT(cell_location_of_image_name) directly, does not work and throws up an error. The error when using INDIRECT is "This formula is missing a range reference or a defined name" and the error when I use the Name reference is "Reference isn't valid". So it is able to change the image if I type in the Named reference name of the image directly but not using the other methods, which allows it to be dynamic.

I am trying to keep as much of this out of VB since I am doing this for other people to use and they won't understand how it works. I would rather not have to change the image in VB, but can if I have to. I am just wondering why it works fine when the Picture is not in the Chart but gives an error when it is in a Chart.

I am using Excel 2019.

[Andy Pope]

Based on your description try this version which uses the INDEX formula to locate cell to display.

The named range is USE_PIC, and you will need to use Name Manager to see formula used.

[RuKeBo]

Thank you for your reply. I see how you have done this in the file you attached. This does not work for my example but this is because I omitted one piece of important information that I didn't see as critical at the time until I saw your example. My images on on a separate sheet in the same workbook. If I simply use the INDEX formula in my named range, as I have it now, instead of INDIRECT, it doesn't work, I get the same error, "Reference isn't valid". If I put the images on the same sheet and update the INDEX formula accordingly, it will work. I expect the INDIRECT would also work but did not try it.

The issue seems to be that the images are on a different sheet. Again, this works with a simple Picture outside a Chart, just not with the Picture in the chart. So I guess it is having issues in the Chart with the images being on a different sheet.

[Andy Pope]

Works for me on separate sheet within picture embedded in chart object.

[RuKeBo]

This is interesting. There were two pictures in your file, one was on the chart, one was in. The one in the Chart did not have the named reference. Regardless, it still works fine if I add the USE_PIC named reference to it.

I added another chart to your document, inserted a picture, and added the name reference to it and it also worked. I did the same thing in my file and it did not. The one thing I notice, and not sure this has anything to do with it but it is different, is that when I add the scatter chart or click on the existing one in your file, the Chart Elements, Styles, and Filters icons pop-up at the outside upper-right of the Chart. When I select or add a chart to my document, it does not.

[RuKeBo]

I added a new Sheet in the same workbook, added a chart, and it did have the Chart Elements, Styles, and Filters icons pop-up. Not sure why but not going to focus on that now.

I found this from adding a Picture in the Chart on the new sheet, because I had a typo, but this works on the existing Chart/Picture as well. If I use the cell value itself for the name reference, without the INDIRECT or INDEX formula, I can use then use the name reference for the Picture, of course it just puts the text from the cell, not the image. But, it I then change the name reference to have the INDIRECT formula, it works, it replaces the text in the Picture with the image referenced in the cell. But, as soon as I go back and edit the formula for the picture, I get the error. I thought I had this working before but then wasn't sure. I was fighting with this problem, then it just started working and I went on to something else in the workbook. As some point, it wasn't working, the formula for the picture was gone, when I tried to put it back in, it didn't work.

I guess I should try making a complete new workbook from scratch and just add the chart then see as I add things back to it, what might be causing the issue.

[RuKeBo]

I guess the INDIRECT formula does not work even in your file, except for that loop hole that doesn't seem to stick. I can change the USE_PIC named reference to use INDIRECT and it will work as long as I do that first and don't touch the picture. But if I go back and retype =USE_PiC into the formula for the Picture in the chart, it gives an error and won't let me keep it, so it breaks. I am curious if anyone knows why this is so.

I currently have my images in 5 columns of 5 rows in my sheet. They are referenced by named references. I put them all in a single vertical column to use the INDEX properly, and after somehow fiddling, got it to work.

would still like to know the reason the Indirect doesn't work.

Thank you for your help.

[Andy Pope]

I changed USE_PIC to be
=INDIRECT("Sheet1!R"&(Sheet2!$N$4+3))

and it worked.

You will have to post your file for us to investigate

[RuKeBo]

There are a few issues with this. Open your file "1335059b.xlsx‎". Does it have two pictures now? It does when I open it. One is imbedded in the chart and the other is on top of it. The Picture that is not in the Chart has =USE_PIC assigned to it but can be dragged anywhere. The one imbedded in the chart does not have any formula. The one that was originally added to the chart looses it's formula. They both seem to have the same name.

Delete the picture not imbedded in the chart, add =USE_PIC to the picture in the chart, it should change when the drop down is changed. Save the file, close it, then reopen it, does it add another picture again? For me it does, in my files I was working on as well. This is not usable since you would have to remove the stray picture every time then reassign the formula.

Edit: If you remove the =USE_PIC in the formula of the Picture in the Chart and then save it and reopen it, you do not get another picture.

[RuKeBo]

Also, if you create named ranges for each picture, highlight S4 thru R8, then use Defined names, and "Create from Selection" and Right Column. You should have Name "Cat" assigned to "=Sheet1!$R$4", "Dog" to "=Sheet1!$R$5", etc. Then change USE_PIC to =INDIRECT(Sheet2!$L$4) so that when you select "Cat", it will display the Cat picture, etc. As long as you already have the Picture formula as =USE_PIC, this should work. Now change the formula for the Picture, just retype it or something, and you should get an error. This is the issue I was having. I could negate the named picture name as you did and reference the cell and it should work.