Aug 16 2022 05:44 PM
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!
Aug 16 2022 09:27 PM
@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.
Aug 17 2022 06:28 AM
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?
Aug 17 2022 01:45 PM
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.
Aug 18 2022 11:19 AM