Forum Discussion
Implicit intersection operator: @ breaking my formulas
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.
In my workbook, it changed several formulas like "=IF(G2 = "Yes", "", D2)"
to
"=IF(G2 = @Yes,"",D2)"
Completely breaking the functionality of my workbook. In each case, the referenced value was *NOT* an array, nor part of an array formula.
Fortunately, the bulletin seems to be inaccurate in at least one regard. On save/re-open, my manual fixes of the formulas were not converted to legacy array formulas.
A method of disabling this automatic conversion should have been provided, along with an explicit warning to the user in the application that this was taking place at the time it happened.
Here is as much as I can provide without sacrificing proprietary company information. See screen shots attached:
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 @ 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 @ operator...... or find a way to rewrite this formula that alleviates the issue.
- Afraid that there's not much I can suggest. I totally appreciate your need to protect proprietary data. Just looking at the screen prints, though, doesn't help really grasp what the macros do, for just one example.
Now, you clearly know your Excel--couldn't have written that formula if that weren't the case--so although I'm going to ask a question, I'm NOT questioning your abilities with Excel.
I will offer this observation based on past experience with another internal company process, back before I retired: somebody in IT had written a very elaborate Visual Basic program to process data from an SQL query of a mainframe database, after the data were imported into Excel.
I re-wrote the spreadsheet to use built-in Excel functions (some from the D_____ series of functions, for example, designed to extract data from data tables). My spreadsheet using existing Excel function, without any macros, delivered the needed results in a fraction of the time of the macro-based system.
This of course doesn't mean the same is true for your situation at all.....and I realize that fully. But I offer it just as a question: Excel's built-in functions are amazingly powerful, so have you considered an altogether different way to use Excel to meet your particular business need? Is the macro really needed to "scrub" values from one place and put them in another?Hi mathetes,
I really appreciate your thoughts and insight on the situation. The Excel file was built before I took over the responsibility for the tool. I have since been able to refactor the Visual Basic macros scripts and refine the formulas throughout the workbook. The file operates much faster and is much lighter. The team that uses this tool is very happy with the progress! The file does the job right now, and I'm currently taking the approach of: "if it's not broken, don't fix it".
However, this new Microsoft update threw a wrench into the formula functions. I was able to rewrite the formula to work around the "@" symbol operator. The only change for users is that they will need to type the word "zero" if they want to lock a forecast at 0. This is because the "@" symbol operator causes confusion for some functions' ability to differentiate between '0' and a blank cell.
I also noticed that, without any updating (that I know of), Excel stopped inserting the "@" operator in the formulas. The Excel version is still version 1910, and I've also verified this change on other affected machines with version 1910. Very strange. I'm still waiting to hear back from Microsoft on my support ticket. I'm assuming I'm not the only one to complain about the "@" operator....
Regardless... here's the updated formula for the work-around:
=IF(I2<>"Target Season","",IF(IFERROR(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1),"y")<>"y",IF(UPPER(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1))="ZERO", 0, IF(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1)>0, INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1),IF(E2=0,0,IF($G2="","",$G2)))),IF($G2="","",$G2)))
