I’ve spoken with Microsoft Excel customer support about this and there are no other known instances of the problem my team and I are having with two of our templates, which I designed myself from scratch about a year ago. They suggested I post this thread to a handful of forums, in hopes that some awesome software genius out there can help me out.
One crucial thing to note before I get into the detail of what’s happening is that we *only* encounter the issue once the file(s) are uploaded to SharePoint and accessed via a web browser (as opposed to opening from synced folder in Windows Explorer). In other words, if the file is saved locally on the machine or our internal server, the issue does not occur. Another thing to note is that it doesn’t *always* happen, and I haven’t been able to pinpoint the exact timing of when it actually takes place, since it’s an “invisible” problem. Which will make sense by the time you get to the end of this post. Anyways…
The two workbooks I designed each serve very different purposes, but are alike in the sense that they’re set up to automatically create a print-friendly, clean, professional looking report, the PDF form of which is the finished product that we send to our clients. These templates have about 20 separate tabs a piece, but we’re only going to focus on the three that are relevant to my problem. Those are:
1st Tab: Deal Inputs – This is where users enter/drop all the raw data for any given project, and set preferences for how things should calculate/display, etc. The sheet includes a bunch of stuff, but the problem I’m having only involves three ranges of merged cells. I’ve defined each of these separately as named ranges: ClientLogo, FrontCover, and BackCover. A different photo (jpg/png) gets inserted in each of the three named ranges, and sized to fill the entire range, horizontally and vertically. Moving on…
2nd Tab: Front Cover – This where the problem starts. It’s also page 1 of the final package we PDF and send to the client. When I first designed this template in Excel, after creating the Named Ranges listed above, I then inserted two new photos onto the Cover tab. I sized those appropriately to fit well with the rest of the auto-generated content included on the cover page (which also pulls from the Deal Inputs Sheet, among other places). After inserting, I selected the 4th photo just inserted, and in the formula bar (with the *photo* on the cover tab selected, I typed my reference to the first named range: =ClientLogo. I hit enter, and yay – my thumbnail client photo image that’s plugged into the Deal Inputs tab automatically takes the place of the original 4th image inserted, and is properly sized/positioned within the cover itself automatically.
I repeat the same process for the 5th photo I’ve inserted on the Cover tab, only for this one, I reference the 2nd named range: =FrontCover. Once again, the thumbnail from Deal inputs takes the original image’s place on the cover, and automatically resizes to fit the page.
3rd Tab: Back Cover – The problem continues here, and like the Front Cover, I followed the same process to initially set up the automatic back cover image here, using a 6th photo inserted on the Back Cover tab. This one references the third named range from Deal Inputs: =BackCover.
PROBLEM: Once the file is uploaded to/accessed from SharePoint via web browser by my colleagues and I, seemingly at random, I’ll notice that the file size has suddenly become massive (the first time I caught this, it was over 35mb, and usually is only ~6kb). Unless you’re paying attention to the file size, it’s impossible to see that there’s even a problem, which is that the “auto-pics” on the front and back cover tabs are duplicating themselves, seemingly at random, one on top of the other (so you can’t ever *see* there’s more than one image until you select the what appears to be the only one and drag to either side and reposition it, revealing a replica of it beneath where the one on top was moved from. If others on my team who are less Excel-savvy access the file multiple times daily over the span of a few weeks but it’s been a while since I’ve opened it myself, the images sometimes wind up having been duplicated literally THOUSANDS of times over. To fix it/clean up the file size, I just need to do a simple ‘Select All Objects’ and press delete, but my God if it’s not annoying AF.
The original/source photos that users insert onto the Deal Inputs tab are never duplicated. *MY* company logos are never duplicated on the Front Cover or Back Cover sheets (because those are also just regular photos dropped onto the worksheets, rather than photos auto-generated via reference to named range. It’s only those three ‘Result Photos’ contained on the covers - =ClientLogo, =FrontCover, and =BackCover. And again, it *only* happens in versions that are shared/saved to SharePoint.
Someone PLEASE help me figure out what is causing this. If you open the attachment, note that it only includes the three tabs I am referencing, so links/some named ranges/conditional formatting will be disabled.