This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

New Bug Encountered: Spreadsheet Formula Not Updating in a field

Posted on 09-05-2019 06:41 AM
Kanha_PM_Office
New Member
Reply posted on --/--/---- --:-- AM

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.

2X_5_5f2ff4517be092eb78a8134675bd853d91511aa5.png

Solved Solved
0 12 1,337
Topic Labels
Jump to Solution
0 Likes
1 ACCEPTED SOLUTION

Posted on --/--/---- --:-- AM
Phil
Staff
Reply posted on --/--/---- --:-- AM

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.

View solution in original post

Jump to Solution
12 REPLIES 12
Top Labels in this Space