Excel changes array formulas when creating new worksheets

[mm_mmmm_mm]

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

[#2]

Hi@mm_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)))