Problem pushing spreadsheet formula to appsheet R1C1 formula

[Kenny]

Hi. Am going round in circles now and need help please someone (everyone).
I have an spreadsheet formula that is not getting pushed over to appsheet. Keeps coming up with an error and asking me to compare it to the R1C1 formula that is generated. As a result, when editing the column, the spreadsheet formula box is not filled. I have stripped back the spreadsheet code as much as possible so only use MAX and INDEX function together. The spreadsheet formula =IF(F810<>"", MAX(FILTER($D$2:D, F810=$F$2:F)), “”) looks in a column of entries of pets. Each pet has a unique hospital number, which can have multiple visit dates. The formula matches the hospital number (F) and then will pick out the oldest entry date (D) using MAX. The above formula works nicely but will not be pushed into appsheet. An alternative formula does get pushed such as =IFERROR(IF(D810<>"", INDEX($A$2:BM, MATCH(1, (F810=$F$2:F)*(BN810=$D$2:D),0), 8), “”), “”). This formula Matches the oldest entry date and pushes out some other column. So, in order to help can anyone provide and alternative formula for the spreadsheet or let me know why the shorter formula cannot be pushed to appsheet please. Thanks.

[Kirk_Masden]

Hi @Kenny! Welcome to the community!

I may not have enough knowledge to help, but I’ll offer some ideas and then perhaps other people with more knowledge will chime in.

In my experience, we don’t usually need to edit the R1C1 formulas ourselves. Just to be sure we’re taking about the same thing, here’s such a formula from one of my apps:

These formulas are created automatically. They can get sometimes get messed up, however, when we change columns or formulas in our spreadsheet. The best solution in my experience is “Regenerate structure”.

This should fix your R1C1 formulas, provided you have data in your spreadsheet. If the spreadsheet is empty, then you won’t get the desired result.

Hope this helps!

[Kenny]

Thanks @Kirk_Masden.
Have regenerated structure over and over again while making changes to the spreadsheet formula to try amd find a solution. Spreadsheet has 810 lines of data (columns A:BS). All my other formulas get changed to R1C1 in my other apps. This is the only formula that aludes me. Spreadsheet works great, with all data within getting updated as new entries added. Not sure why one formula works but not the other. I wondered if MAX and INDEX function not compatible together in appsheet. Have tried using SORT instead of MAX (SORT in spreadsheet has different parameters compared to appsheet version). Can’t seem to crack the code after 8 hours plus!!

Thanks for the suggestion and trying to help.

[Kirk_Masden]

Kirk_Masden:

I may not have enough knowledge to help,

As I suspected, I don’t know enough to help. I hope someone else can. @Aleksi, @LeventK, or @Steve (among others) should be able to help you straighten this out. Good luck!

[Aleksi]

Do you have the “same” sheet formula in every row?

[Kenny]

The one formula that does not work is the a unique sheet formula in the spreadsheet. The other formulas that do work as stated in start of thread are very similar (which I guess is why they work)

[Kenny]

Can you try the spreadsheet formula =IF(F2<>"", MAX(FILTER($D$2:D, F2=$F$2:F)), “”) with say 5 lines of made up data to see if this works for you and pushes the formula. Maybe there is a bug in my table. I just cannot solve it.

[Kenny]

I have just realised what your question actually meant…Yes there is the same sheet formula in each row using e.g. $A$2:A to go down the rows.