Excel dynamic lookup image keeps duplicating itself - but the cause was because of array formula

[yonghanwu]

I made a file using dynamic images to display an image based on another cell value. I used the method described in this video: https://youtu.be/wlW2UKml9CY. Instead of a drop-down name picker, the dynamic names cell was by using a randbetween() function to pick from a list of names that are based on numbers. So the image is both dynamic and randomly picked.

However my display image keeps duplicating itself multiple times, lumping on top of the same original image. I only found out because I accidentally moved my intended image, only to find 3 more such images right beneath it. This was causing my file to grow really big and causing crashes. I've gone through with Office 365 support and in a screen share session tested various ways to achieve this.

It turns out if I used array formula to generate these random names, my images duplicates itself multiple times. But if instead of using array, I use a random name for cell needed individually, then the duplication of images does not occur.

This is very strange because cells where the names are will appear exactly the same (i.e. randomly generated), no matter whether these names are calculated cell by cell, or calculated as a single-line of array calculation. Why would array calculation cause the images to duplicate themselves?

Below is a link to the file you can download and investigate. 

link to the "troubled duplication" file (link expires 28Feb2023) 

The array formula was in 'Generate'!D3. For Column D, if instead of using array, you calculate cell by cell, then duplication problem does not occur.

Tech support said I should post this in the community here and Software Engineers would get back to me, either with explanation why this should be the case, or find out maybe array has some other hidden unintended implications. Thanks!

[#2]

@yonghanwu Downloaded your file, removed the duplicate pictures. Changed nothing in the formulas or named ranges. Hence the dynamic array formula in D3 remains unchanged.

Pressed "Calculate now" many times. No duplication of the pictures occurred. Tested on both PC and Mac.

[#3]

@Riny_van_Eekelen 

Thanks for helping me test this! It's very strange. The duplication almost always happened for me. It happened a few times even when I changed the array formula into single-cell formula.

Done another experiment, and it seems the duplication happens when the calculation option is set to "manual", then somehow upon file open, sometimes the images are doubled up. If it's set to "automatic" then it was not producing any duplicates. Maybe manual calculation is the culprit?

[#4]

To add to my previous post: 

1. if you don't see the duplication of images, it's because they are exactly on top of each other. Select the image on the top and move it a little, you'll see the original image lying right underneath it.

2. If you delete the duplicates, then click "Calculate now", duplication won't happen right there and then. It seems to always happen after I saved, closed and re-opened the file. I've been keeping deleting these duplicates, and they always pop back up after I opened the file again. Very frustrating.

[#5]

@yonghanwu Tried that as well. Can't replicate the error.

[#6]

I don't always get the error. I start to notice that I always have duplicates when Excel crashes by shutting itself off. And Excel crashes most likely if I had just done a bunch of calculations and then Ctrl+P and clicked "generate print preview".

It almost seems what Excel does when switching dynamic images is to make a copy then delete the old one. So if during the process of switching to different images, if it gets interrupted (crashes), then both copies of the images are kept -- that's my guess. If Excel crashes, when I re-open the file I always (I think) see doubles of the same images. If I wait a minute or two before I use print preview, it seems to reduce the chance of crashing.

The sample file I linked here is smaller so it is less likely to crash. My other file is a bit bigger (7mb). But I am surprised that Excel had trouble handling a 7MB file. My CPU is 2.6GHz, RAM 8GB, 64-bit. Windows 10 Pro; Hard drive 256GB with still 67GB left. And these crashes happened when I just had Excel and Edge running with a couple of tabs.