Forum Discussion
Image Function adding Implicit intersection At operator Image Function not working
The Image function is not working properly as part of an automated template definition that is adding an At sign when the Excel opens the file. We believe this is due to the following upgrade in Excel's formula language https://urldefense.com/v3/__https:/support.office.com/en-us/f1/topic/ce3be07b-0101-4450-a24e-c1c999be2b34__;!!JtwtF54jn92Iq-I!b6ZvCaGfKeatI4kg4fMNLwZkMNghuqXD6ye9DQtwF42iodhRviOwxBINbb78q8lNRrfqeYO4ZTOqZuR4i0rXUNY9GLw$
The Image function should not be treated as an Array, but we even coded the Image function as an array and still are having problems. See example here, where the formula is an Array but the function does not work until you "Focus" on the Cell and hit "Check"
click https://urldefense.com/v3/__https:/salsify-dandelion.s3-external-1.amazonaws.com/1/6462/user_data_export/1/2023/04/04/17*3A30*3A36-1079eea9/export.xlsx?response-content-disposition=attachment*3B*20filename*3D*22KHC-*202023*20US*20Retail*20Digital*20Catalog*20Product*20Feed_2023_04_04_17_30_36_UTC.xlsx*22&response-content-type=application*2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIARRX37GWVL5YUBHGV*2F20230404*2Fus-east-1*2Fs3*2Faws4_request&X-Amz-Date=20230404T173036Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=84f5e0455ae7348d215e9eb505d7794782ef653d8e816edab79480e3415dc2d3__;JSUlJSUlJSUlJSUlJSUlJSUlJQ!!JtwtF54jn92Iq-I!cbIFwMV_08pII0xRcv6RR6v8LLIfsTv8USC-L5R42gcqZccyWoAU4txG6fmgvABtKueILhg8j8gPtstEpQvVXXLJDV_5sx4B$ to access file, see cell H for the formula.
We cannot use VBA\Macro to resolve the issue.
Any help would be greatly appreciated.
Jeff_Deutch300 SOLVED - formula should be #=_xlfn.image(x,x,x,x,x) - this works for the Caxlsx XML
Appreciate everyone's support in helping resolve this issue.
From Microsoft Support -Test Summary: Test File https://urldefense.com/v3/__https:/salsify-dandelion.s3-external-1.amazonaws.com/1/6462/user_data_export/1/2023/04/24/19*3A08*3A58-10a33c9c/export.xlsx?response-content-disposition=attachment*3B*20filename*3D*22KHC-*202023*20US*20Retail*20Digital*20Catalog*20Product*20Feed_2023_04_24_19_08_58_UTC.xlsx*22&response-content-type=application*2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIARRX37GWVL5YUBHGV*2F20230424*2Fus-east-1*2Fs3*2Faws4_request&X-Amz-Date=20230424T190858Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=ea2b3974870e0e990178369cdd6f05da7480b52116cc9e07fe5f05c363418c5b__;JSUlJSUlJSUlJSUlJSUlJSUlJQ!!JtwtF54jn92Iq-I!aCVhjnBQZNjS82lkmMca6Qnv_MxsKLH10bqpZJmih2oGWUXvo8a_KEg45LLkVl_iftNuuP8PfxijSlJM3qQxTp6BzJSVJQ4D$
21 Replies
++1 who:
- Finds the time to raise an issue
- Finds the time to say this & that doesn't help/work
- Does not find the time to provide feedback/mark solution when one potentially exists
=> NET RESULT: next time someone else has a similar issue, actual contributors to this forum will re-invent the wheel
(Thank You)
LZ and Sergei,
I apologize for not responding sooner, but unfortunately, we are still unable to resolve the Image formula issue systematically, so we created a manual workaround. We tried your recommendations, we also worked with Microsoft Premier support along with the Vendor who creates the automated spreadsheet. We did learn that the spreadsheet is leveraging Caxlsx, which may be impacting the Image function. Here is our manual work around. See Column H that goes to a video telling users what to do. Any more suggestions would be appreciated.Quickly read a few things about Caxlsx (unknown until now)
One thing that remains unclear to me: is the xlsx you attached "generated" by Caxlsx?
Running 365 Version 2303 Build 16227.20258 Current Channel / Windows
Opening the file you shared here is what I get (IMAGE function is avail. here no problem):
From there, if I select i.e. H2 and press Enter in the formula bar, I get:
Choosing Yes I get the expected Image :))
I suppose (???) this +/- reflects the issue you raised... Assuming this does, would you mind revising your formula in column H
From:
=IMAGE(AE2:AE2,A2,3,100,100)To (this fixed the issue here):
=IMAGE(AE2,A2,3,100,100)and let us know how it goes
IMHO, it shall work in any case if only the file is opened in proper Excel version. Using of implicit intersection operand shall not cause #NAME? error.
Agree, this should work in any case (but obviously this doesn't...)
I didn't want to confuse Jeff_Deutch300 with the following that also addresses the issue here (but raises question):
- If image not works,how about using html img tag to display photo?
- thanks Sergei, can you let me know what version of Excel you are using and if you using Windows or Mac...?
Excel 365 on Windows desktop, Current channel. Setting which Patrick2788 mentioned is not unchecked - it only generates an alert which I prefer to keep.
It might help to see the entire formula being used but you could try unchecking this option.
- thanks Patrick, that didnt help, but can you share the rest of your formula settings...?
