Spreadsheet Formula #REF Problem

[Kyle_Richardson]

I’m using O365 Excel via SharePoint.

I have a formula that’s correct in my spreadsheet and performs as intended, but when I attempt to sync any changes I get an error.

I’ve regenerated the column several times to make sure it’s picking up the formula correctly.

Maybe I’m missing something here but it keeps showing a #ref for one of the column references.

The formula is consistent for the entire column.

My Excel Formula:
=IF(K2=TRUE,VLOOKUP(L2,E:CI,83,FALSE),IF(OR(AC2>=N2,CH2=“TRUE”,CH2=TRUE),TRUE,FALSE))

My Spreadsheet formula in AppSheet:
IF(RC[-76]=TRUE,VLOOKUP(RC[-75],C[-82]:C,83,FALSE),IF(OR(RC[-58]>=RC[-73],RC[-1]=“TRUE”,RC[-1]=TRUE),TRUE,FALSE))

The formula in the error message:
=IF(1200=TRUE,VLOOKUP(1200,:A,83,FALSE),IF(OR(1200>=1200,#REF!1200=“TRUE”,#REF!1200=TRUE),TRUE,FALSE))

Thank you,
Kyle

[Kyle_Richardson]

I’m still experiencing this issue. Any thoughts from anybody is appreciated.

Formula works great in my spreadsheet. Won’t sync in AppSheet.

-Kyle

[Lynn]

Hi @Kyle_Richardson Have you tried using just an Appsheet formula?

[Marc_Dillon]

I’d agree with Lynn, translate your formula to Appsheet expressions, and delete it out of the spreadsheet.
Also this article might help.

help.appsheet.com

Spreadsheet Formulas

How formulas you write in your spreadsheet impact your app creation process

[Kyle_Richardson]

An app formula is not an option due to how the column needs to be updated based on fields from different tables. The column in question needs to calculated without the specific record being updated through the app.

I ended up solving the problem though.

It was an issue with a slice that included the column with the spreadsheet formula.

I was able to remove the column from the slice and now everything syncs properly.

I think it was an issue with not all of the other columns referenced in the formula being present in the slice.

Perhaps there’s a better, more technical explanation for what I’m trying to say.

Thanks everybody!