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.
@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”))))
@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.
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 ?
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |