@ appears in my sheet reference formula - macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1407166%22%20slang%3D%22en-US%22%3E%40%20appears%20in%20my%20sheet%20reference%20formula%20-%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407166%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(Notes!K5%3AK122%2C(MATCH(1%2CIF(Notes!%24H%245%3A%24H%24122%26lt%3B%3DSheet2!C6%2CIF(Notes!%24I%245%3A%24I%24122%26gt%3B%3DSheet2!C6%2C1))%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20the%20above%20formula%20and%20i%20am%20trying%20to%20populate%20the%20same%20in%20a%20cell%20using%20a%20macro%2C%20but%20when%20i%20run%20the%20macro%20it%20changes%20to%26nbsp%3B%3DINDEX(Notes!K5%3AK122%2C(MATCH(1%2CIF(%40Notes!%24H%245%3A%24H%24122%26lt%3B%3DSheet2!C6%2CIF(%40Notes!%24I%245%3A%24I%24122%26gt%3B%3DSheet2!C6%2C1))%2C0)))%20and%20the%20formula%20doesn't%20work%2C%20but%20i%20remove%20the%20%22%40%22%20it%20works%20fine.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20the%20%22%40%22%20is%20coming%20up%20%3F%26nbsp%3B%20%26nbsp%3BI%20think%20its%20the%20new%20way%20of%20referencing%20a%20sheet%20in%20the%20new%20excel%2C%20but%20not%20sure%20what%20should%20be%20the%20correct%20version%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1407166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1407573%22%20slang%3D%22en-US%22%3ERe%3A%20%40%20appears%20in%20my%20sheet%20reference%20formula%20-%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675083%22%20target%3D%22_blank%22%3E%40Christsake%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20new%20feature%20introduced%20in%20Office%20365%20Version%201912%20wich%20is%20used%20with%20dynamic%20array%20formulas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20an%20article%20about%20this%2C%20it%20is%20in%20Turkish%20however%20you%20can%20use%20translated%20page%20as%20below.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftranslate.google.com%2Ftranslate%3Fsl%3Dauto%26amp%3Btl%3Den%26amp%3Bu%3Dhttp%253A%252F%252Fexcelileharikalar.com%252Findex.php%252F2020%252F02%252F20%252Fbelirsiz-belirli-kesisim-ve-isleci%252F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftranslate.google.com%2Ftranslate%3Fsl%3Dauto%26amp%3Btl%3Den%26amp%3Bu%3Dhttp%253A%252F%252Fexcelileharikalar.com%252Findex.php%252F2020%252F02%252F20%252Fbelirsiz-belirli-kesisim-ve-isleci%252F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi everyone

 

=INDEX(Notes!K5:K122,(MATCH(1,IF(Notes!$H$5:$H$122<=Sheet2!C6,IF(Notes!$I$5:$I$122>=Sheet2!C6,1)),0)))

 

So I have the above formula and i am trying to populate the same in a cell using a macro, but when i run the macro it changes to =INDEX(Notes!K5:K122,(MATCH(1,IF(@Notes!$H$5:$H$122<=Sheet2!C6,IF(@Notes!$I$5:$I$122>=Sheet2!C6,1)),0))) and the formula doesn't work, but i remove the "@" it works fine.  

 

Any idea why the "@" is coming up ?   I think its the new way of referencing a sheet in the new excel, but not sure what should be the correct version

1 Reply

@Christsake 

This is a new feature introduced in Office 365 Version 1912 wich is used with dynamic array formulas. 

 

I have written an article about this, it is in Turkish however you can use translated page as below.

https://translate.google.com/translate?sl=auto&tl=en&u=http%3A%2F%2Fexcelileharikalar.com%2Findex.ph...