Image Function adding Implicit intersection At operator Image Function not working

[Jeff_Deutch300]

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  Support page (office.com)

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 here to access file, see cell H for the formula. 

We cannot use VBA\Macro to resolve the issue.

Any help would be greatly appreciated. 

[#2]

@Jeff_Deutch300 

It might help to see the entire formula being used but you could try unchecking this option.

[#3]

@Jeff_Deutch300 

All works in my case. On which Excel you are?

[#4]

thanks Patrick, that didnt help, but can you share the rest of your formula settings...?

[#5]

thanks Sergei, can you let me know what version of Excel you are using and if you using Windows or Mac...?

[#6]

@Jeff_Deutch300 

Excel 365 on Windows desktop, Current channel. Setting which @Patrick2788 mentioned is not unchecked - it only generates an alert which I prefer to keep.

[#7]

If image not works,how about using html img tag to display photo?

[#8]

@Jeff_Deutch300 

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

[#9]

@L z. 

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.

[#10]

@Sergei Baklan 

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):

[#11]

@L z. 

I'm not sure if + changes something for that function. And @IMAGE(AE2:AE2,.. shall work the same way as IMAGE(@AE2:AE2,...

[#12]

@Sergei Baklan 

Agree, this should work but for some reason(s) the theory doesn't seem to apply here and for @Jeff_Deutch300 

The IMAGE function is currently available in 365, Web, IOS and Android only & nothing - in the formula exposed by Jeff - requires the intersection operator. If it was me I would look at elimating the intersection operator and the following seems to do it:

=IMAGE(AE2,A2,3,100,100)

(my view only and not my call) 

[#13]

@Jeff_Deutch300 

++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)

[#14]

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.

[#15]

@Jeff_Deutch300 

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?

[#16]

https://github.com/caxlsx/caxlsx created the XML that was used to create the XLSX spreadsheet that i attached. Somehow, Excel is not recognizing the Image formula the when the spreadsheet is opening and is requiring the 'check' formula to recognize and process the image function. Is it generating the images for you without the check formula? As it may be a setting on our version of Excel.

[#17]

Hi @Jeff_Deutch300 

Thanks for https://github.com/caxlsx/caxlsx - interesting reading. Not sure this is relevant but there are interesting things in sections Security & Known Software Interoperability Issues

That won't help fixing the issue and I can be wrong but I feel you face an interop. issue between "products". Assuming I'm not wrong, does the problem come from Excel or from the XML, only dev. would be in a position to say so (IMHO)

Given you involved MSFT Premier I guess this was tested but jut in case, what happens (do you get the @ operator in front of the formula?) if Caxlsx fills H2 with:
=IMAGE("https://images.salsify.com/image/upload/s--uF3yfZrg--/t_salsify_thumb/v1422298774/thumb_default.jpg","00021000007301",3,100,100)

Also, if it was me I would raise the issue on https://stackoverflow.com/questions/tagged/axlsx

[#18]

thanks LZ - we are 99% sure that Excel is adding the "@" sign as follows .=@IMAGE(AE2,A2,3,100,100) thank you for the stackoverflow suggestion

[#19 · Accepted solution]

@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 here

[#20]

Hi @Jeff_Deutch300 

THANKS MUCH for posting this important update + Glad you now have a solid solution

@Sergei Baklan: could you mark the solution please? Thanks