Forum Discussion

mm_mmmm_mm's avatar
mm_mmmm_mm
Copper Contributor
Dec 06, 2023

Excel changes array formulas when creating new worksheets

Dear forum,

 

I have a strange problem.

I have the following formula:

 

=FILTER(SAPDATA_ScannerVKP;
WENN(COUNTA(L2#)=1;SAPDATA_ScannerVKP[Kunde]=INDEX(L2#;1)))

 

 

(contains references to the named table "SAPDATA_ScannerVKP")

(German formula notation)

 

As soon as I create a new worksheet, Excel automatically changes the formula as follows:

 

 

=@FILTER(SAPDATA_ScannerVKP;
WENN(COUNTA(L2#)=1;SAPDATA_ScannerVKP[@Kunde]=INDEX(L2#;1)))

 

 

Excel has distributed @ characters in the formula!

 

As a result, the formula no longer works, but displays the #'Value! error instead.

I then have to manually change the formula back to its original state.

 

Why does this happen? How can I prevent this?

 

Regards

 

 

excel
Formulas and Functions
office 365
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Dec 16, 2023

    Himm_mmmm_mm 
    you may try to change/revise your formula into absolute reference ( $A$1 ), instead of using structured reference ( TableName[ColumName] ).

    Example:

    =FILTER(SAPDATA_ScannerVKP, 
IF(COUNTA($L$2:$L$100)=1, SAPDATA_ScannerVKP[$A$2:$A$100]=INDEX($L$2:$L$100,1)))

Resources