New entry with spreadsheet formula produces e...

[Stefanie_Dietz]

New entry with spreadsheet formula produces error

I have several tables where some columns contain spreadsheet formulas. In some cases (maybe depending on the type of formula? Haven’t checked them all yet) when I enter a new item in the app, it does fill in the spreadsheet formula for the item’s new row, but it messes the formula up on the way.

The SUMIFS formula =SUMIFS(‘Rezept Zutaten’!I:I;‘Rezept Zutaten’!J:J;B236;‘Rezept Zutaten’!G:G;“TRUE”)+sumifs(L:L;B:B;B236;M:M;“TRUE”)

becomes this mess, which then produces an error in the sheet: =SUMIFS(‘Rezept Zutaten’!C[-8]:C[-8];‘Rezept Zutaten’!C[-7]:C[-7];B237;‘Rezept Zutaten’!C[-10]:C[-10];“TRUE”)

Why does this happen and how can I fix it? I don’t want to have to write a script to fill down formulas every time I enter a new item.

[Bellave_Jayaram]

@Stefanie_Dietz, If you put the following formula in row 1, you can delete the sheet formula in the AppSheet column and there won’t be a need to copy down the formula to new rows.

=iferror(ArrayFormula(if(row($A$1:$A)=1,“COLUMN HEADER”,SUMIFS(‘Rezept Zutaten’!I:I;‘Rezept Zutaten’!J:J;B236;‘Rezept Zutaten’!G:G;“TRUE”)+sumifs(L:L;B:B;B236;M:M;“TRUE”))))

[Stefanie_Dietz]

@Bellave_Jayaram Thanks! Hadn’t thought of an array formula before. Although changing the way the spreadsheet works just because AppSheet is not able to properly insert the formulas can not be a long-term solution methinks.

One problem though - the array formula throws an error… I’m not an expert on array formulas, so any help fixing it would be approciated.

[chboktor]

I also have the same issue i think it has to do with the Type we choose for the column or the settings i fixed it once while trying everything possible but lost again did u get lucky maybe ?