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:





(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:






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?





1 Reply

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


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