Implicit intersection operator: @ breaking my formulas

Question

Contrary to Microsoft claiming: " It's important to note that your formulas will continue to calculate the same way they always have.", this is not the case. The introduction of the @ symbol in version 1910 is now breaking my formula:

=@IF(I17<>"Target Season","",IF(@IFERROR(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW17,'Lock @ Style-Country'!A:A,0),1),"")<>"",INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW17,'Lock @ Style-Country'!A:A,0),1),IF($G17="","",$G17)))

This formula now returns 0's instead of blanks for the AH:AH range. When I remove the red @ symbols, the formula works again and returns a blank cell when AH:AH is blank. Any ideas on a permanent fix?

Thanks,

David

grosner · Answer

dakuhlke&nbsp;I also see the same issue even with Excel version 2006 build 13001.20384). I see this issue when assigning a formula using VBA Code. Skip to the bottom for my VBA solution.I type in a formula and press Ctrl Shift Enter (CSE) to disable the implicit intersection: My formula looks like this when you view the formula in the formula bar: Note the surrounding curly brackets that signal excel to disable Implicit Intersection.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;={[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([Close Date]),0)-1,0)}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If you Copy and Paste the formula to a different cell, the { } curly brackets will correctly be included in the pasted formula.If you mouse click to select the cell's formula in the formula bar, the { } brackets are immediately removed (the braces vanish when you click into the formula bar, so the resulting pasted formula is also missing the curly brackets. However, the formula copies correctly and&nbsp;does not add in any&nbsp;@ operators. So the resulting calculation is correct.However, try using VBA to assign the formula Excel will add in the @ operator. Although both the source and destination formulas appear correct from within Excel VBA the formula displayed in the Excel formula bar adds back the&nbsp;@ sign. In other words using VBA to copy a formula that contains dynamic arrays will not be correctly interpreted by Excel.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Source formula:
? Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula
=[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([Close Date]),0)-1,0)

'VBA statement to copy formula:
Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula = Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula

'Target formula after VBA statement to copy formula: 
? Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula
=[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([@[Close Date]]),0)-1,0) 'NOTE: YEAR([Close Date]) changes to YEAR([@[Close Date]]).

'AND the cell result will contain the following formula:
=[@[Adjusted All Portfolio]] -@ INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([@[Close Date]]),0)-1,0)

'NOTES: "- INDEX" changes to "-@ INDEX"
'       "YEAR([Close Date])" changes to "YEAR([@[Close Date]])"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I conclude from the above that assigning a formula using VBA code somehow changes the formula to utilize Excel's Implicit Intersection.&nbsp;&nbsp;&nbsp;To work around the issue I use VBA to copy and paste the source cell's formula as follows:&nbsp;&nbsp;&nbsp;&nbsp;Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Copy
Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False&nbsp;&nbsp;&nbsp;ALTERNATIVELY, use VBA's formula2 reference:&nbsp;'VBA statement to copy formula:Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula2 = Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula&nbsp;https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula&nbsp;https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2&nbsp;&nbsp;

simone fick · Answer

Thank you Grosner the formula2 solution worked for me. The @ broke my Index(Match formula when it kept adding @ in front of my Match arrays with VBA. I replaced FormulaArray with Formula2 and it stopped. Thanks!!

dakuhlke · Answer

dakuhlke&nbsp;Update-- when I remove the&nbsp;@ in front of IFERROR, the formula works as normal, even with leaving the&nbsp;@ in front of the IF at the beginning. Why does this occur?&nbsp;

mathetes · Answer

Are you able to provide a sample of this in context -- i.e., can you share a sample of the actual formula in the spreadsheet/workbook of which it's a part.

And, ideally, provide an English language translation of what this formula is doing, the different results it delivers under different conditions. It's hard enough reading nested IF and MATCH conditions in a formula one has written oneself; next to impossible to read what another developer has done.

dakuhlke · Answer

mathetes&nbsp;&nbsp;Here is as much as I can provide without sacrificing proprietary company information. See screen shots attached:&nbsp;The formula in question exists on the RAW DATA tab. This raw data feeds into a pivot table on the "Lock Tab". Users can change values in the RAW DATA tab by updating the values in the AH column on the "Lock Tab". If users don't enter any values in the AH column for the target season, then the RAW DATA tab should display a blank cell. With the new 1910 Version of Excel and the introduction of the&nbsp;@ operator, the values are coming back as "0" instead of blank. So if users are not entering a value in column AH on the "Lock tab", the RAW DATA tab shows as "0". This creates huge problems for macros that are used to copy the values from column "F" on the RAW DATA tab and overwrite the existing data in the "Target Season" field on the "Lock Tab" pivot table. I need to disable Excel from adding this&nbsp;@ operator...... or find a way to rewrite this formula that alleviates the issue.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Implicit intersection operator: @ breaking my formulas

9 Replies

Resources