Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel changes array formulas when creating new worksheets

Copper Contributor

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

 

 

1 Reply

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)))