New Bug Encountered: Spreadsheet Formula Not Updating in a field

[Kanha_PM_Office]

Hi,
I have created a form with a field value having default spreadsheet formula.
When I change the spreadsheet formula, it shows warning & also, the formula is not automatically updated as the new entries are made.

[Aleksi]

It seems that your sheet formulas are not the same in every row. You should check that all formulas are consistent row by row.

[Lynn]

Also you have the same key in more than one row

[Kanha_PM_Office]

Dear Lynn, Aleksi,
I am using the same formula in all columns which it’s not recognising. That’s why I am tying to reach you.
Thank you.

Lynn

    September 6

Also you have the same key in more than one row

---

Visit Topic or reply to this email to respond.

---

In Reply To

Aleksi

      [Aleksi Alkio](https://community.appsheet.com/u/aleksi)

      AppSheet Team




    September 6

It seems that your sheet formulas are not the same in every row. You should check that all formulas are consistent row by row.

---

Visit Topic or reply to this email to respond.

To unsubscribe from these emails, click here.

CONFIDENTIALITY
NOTICE: The information in this email may include opinions or views
which, unless expressly stated otherwise, are not those of the organization or
any other person, and the organization does not accept liability for any
statement opinion expressed. It may contain information of a confidential
or legally privileged nature which should not be disclosed. Nothing in this
message shall be construed as professional medical, psychiatric, psychological,
counseling, or social work advise. Reference to any political, commercial
or non-commercial product, process, or service by trade name, trademark,
manufacturer or otherwise does not constitute or imply an endorsement or
recommendation by the organization. This email has been sent for the sole use
of the intended recipient. If you are not the intended recipient of this
message, you may not disclose, print, copy or disseminate this
information. Usage of the organization’s email domain by sender does not grant the sender any right to legally bind the organization. If you have received this communication in error, please
reply and notify the sender directly and immediately delete the message. The
organization cannot accept liability for any loss or damage you may incur as a
result of virus infection as a consequence of receiving, reviewing or otherwise
using this message. Unauthorized interception or use of this e-mail is a
violation of the law.

[Phil]

I have added a new article that will should help you locate the problem. See https://help.appsheet.com/en/articles/3326638-mismatched-spreadsheet-formulas

[Kanha_PM_Office]

Dear Phil,

Thank you for updating.

But, am facing trouble in Appsheet recognizing the formula automatically.

The sheet formula is same for all rows. (Attached screenshot below)

Also, the Spreadsheet formula is not shown in the Appsheet Columns tab under Auto Compute Field.

Looking forward to your valuable reply.

[Phil]

I can investigate if you provide:

1. Your account id
2. The app name
3. The table name
4. The column name

Before I do that, please verify that you have followed the procedure described in the article.
Having the same formula in “A1” format in every cell does not mean that you have the same formula in every cell of the column. The cells in the column only have the same formula if they have the same formula when displayed in “R1C1” format.

You are using absolute row references in your formula such as “$E$2”. That may be the problem. Looking at the formulas in “R1C1” format will confirm that.

[Kanha_PM_Office]

I did have the same formula in all cells of that particular column.
However not for all the empty cells of that column though. Should I need to drag down the same formula across all the cells of the column?

However, I created new column & appended all old data.

[Phil]

Yes, you want to have the same formula in all of the cells of the column. Entirely empty rows are ignored, but otherwise you need the same formula in every cell in the column.

We compute the percentage of cells in the column that contain each type of formula. We treat an empty cell with no formula as yet another type of formula (that is, the empty formula). Every cell in the column contributes to the percentage for one type of formula or another. That percentage of cells in a column that contain a specific formula needs to be quite high for us to assign that formula to the entire column.

This approach is designed to allow us to ignore the occasional total formula that appear somewhere in the column. Typically the total appears at the bottom, but it might appear elsewhere. But aside from the occasional total formula, the rest of the cells in the column should have the same formula. That allows us to assign the formula to the column.

I have updated the article to explain this better. https://help.appsheet.com/en/articles/3326638-mismatched-spreadsheet-formulas

[Kanha_PM_Office]

This is a cool feature & nice logic Phil.
But, I have found that the formula is not reflected in the new record entered in the previous sheet which I have attached in the trailing message.
I had to remove all the old data & start things from the scratch.
User ID : kanha.men-machines@srcm.org
App Name : Vehicle Reading v1.000549
Table Name : Fuel Request
Column Name : Status
(However, I have changed the table values as mentioned above)

Thank you.

[Kanha_PM_Office]

Dear Phil,
I have tried changing the formula. It’s not getting reflected in the App in the Spreadsheet formula field!
It’s showing formula is not consistent! But, I use the same formula in all the columns.!

[Phil · Accepted solution]

I have enhanced the formula mismatch warning in the Editor to display the formula mismatches in both A1 and R1C1 format. To see the mismatches, compare the formulas in R1C1 format.

If testing goes well, this change will be released on Thursday afternoon Seattle time.

In the interim, I have included the results of the new warning below.

Column ‘Status’ of sheet ‘Fuel Request’ contains mismatched formulas. Compare the formulas displayed in R1C1 format below to see the differences. 55 rows have MultiColumn formulas. 49 distinct formulas are present of which the first 5 are displayed. 7 rows contain formulas like ‘if(AND(ISNA(MATCH(‘Fuel Request’!$B2,‘Fuel Issue’!$E$2:$E352,0)),C2<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!$B2,‘Fuel Issue’!$E$2:$V352,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B2,‘Fuel Issue’!$E$2:$V352,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B2,‘Fuel Issue’!$E$2:$V352,16,FALSE))),“Open”,“Closed”))’ in A1 format and ‘if(AND(ISNA(MATCH(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[350]C5,0)),RC[-17]<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[350]C22,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[350]C22,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[350]C22,16,FALSE))),“Open”,“Closed”))’ in R1C1 format including rows 2,51,52,53,54,55,56. 1 rows contain formulas like ‘if(AND(ISNA(MATCH(‘Fuel Request’!$B3,‘Fuel Issue’!$E$2:$E352,0)),C3<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!$B3,‘Fuel Issue’!$E$2:$V352,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B3,‘Fuel Issue’!$E$2:$V352,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B3,‘Fuel Issue’!$E$2:$V352,16,FALSE))),“Open”,“Closed”))’ in A1 format and ‘if(AND(ISNA(MATCH(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[349]C5,0)),RC[-17]<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[349]C22,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[349]C22,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[349]C22,16,FALSE))),“Open”,“Closed”))’ in R1C1 format including rows 3. 1 rows contain formulas like ‘if(AND(ISNA(MATCH(‘Fuel Request’!$B4,‘Fuel Issue’!$E$2:$E352,0)),C4<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!$B4,‘Fuel Issue’!$E$2:$V352,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B4,‘Fuel Issue’!$E$2:$V352,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B4,‘Fuel Issue’!$E$2:$V352,16,FALSE))),“Open”,“Closed”))’ in A1 format and ‘if(AND(ISNA(MATCH(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[348]C5,0)),RC[-17]<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[348]C22,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[348]C22,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[348]C22,16,FALSE))),“Open”,“Closed”))’ in R1C1 format including rows 4. 1 rows contain formulas like ‘if(AND(ISNA(MATCH(‘Fuel Request’!$B5,‘Fuel Issue’!$E$2:$E352,0)),C5<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!$B5,‘Fuel Issue’!$E$2:$V352,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B5,‘Fuel Issue’!$E$2:$V352,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B5,‘Fuel Issue’!$E$2:$V352,16,FALSE))),“Open”,“Closed”))’ in A1 format and ‘if(AND(ISNA(MATCH(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[347]C5,0)),RC[-17]<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[347]C22,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[347]C22,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[347]C22,16,FALSE))),“Open”,“Closed”))’ in R1C1 format including rows 5. 1 rows contain formulas like ‘if(AND(ISNA(MATCH(‘Fuel Request’!$B6,‘Fuel Issue’!$E$2:$E352,0)),C6<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!$B6,‘Fuel Issue’!$E$2:$V352,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B6,‘Fuel Issue’!$E$2:$V352,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!$B6,‘Fuel Issue’!$E$2:$V352,16,FALSE))),“Open”,“Closed”))’ in A1 format and ‘if(AND(ISNA(MATCH(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[346]C5,0)),RC[-17]<today()),“Not Issued”,if(AND(isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[346]C22,14,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[346]C22,15,FALSE)),isna(VLOOKUP(‘Fuel Request’!RC2,‘Fuel Issue’!R2C5:R[346]C22,16,FALSE))),“Open”,“Closed”))’ in R1C1 format including rows 6.

If you compare the formulas in R1C1 format, you will see that the problems are occurring because the formulas contain values such as “‘Fuel Issue’!$E$2:$E352,0))”. The row number in the value “$E352” is causing the problem. Do you mean “$E$352”? All four references to ‘Fuel Issue’ in the formula have the same problem.

[Kanha_PM_Office]

Yes! I’ve understood the logic now.
I’ve changed the formula & thank you so much for your time & effort.