Implicit intersection operator: @ breaking my formulas

dakuhlke 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.

={[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([Close Date]),0)-1,0)}

• 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 does not add in any @ 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 @ sign. In other words using VBA to copy a formula that contains dynamic arrays will not be correctly interpreted by Excel. 

'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]])"

I conclude from the above that assigning a formula using VBA code somehow changes the formula to utilize Excel's Implicit Intersection.  

To work around the issue I use VBA to copy and paste the source cell's formula as follows:

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

ALTERNATIVELY, use VBA's formula2 reference:

'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

How to add dynamic array formula in Excel VBA 

Formula vs Formula2