As far as I know, what I am trying to accomplish is impossible due to circular referencing. Please let me know if there is any workaround that doesn’t require the user to manually enter the result from one field to another.
Let’s say there are 3 fields on a sheet: two currency fields called USD Price and EUR Price, and a third field is an exchange rate field that automatically populates upon creation of the record.
The user either knows the USD price or the EUR price. If he knows the USD price, he types that into the USD Price field and the EUR field automatically calculates the EUR price based on the exchange rate in the exchange rate field. However, if the user enters the EUR price into the EUR Price field instead, I want the USD Price field to automatically calculate and populate. Automating either one of these calculations is super easy. But when you try to do both, this supposedly creates a circular reference that Ragic will not allow.
As far as I can see, because of the way Ragic calculates fields there should be no infinite loop. Entering a value manually into a field will not trigger a calculation of that field. And the field is ONLY re-calculated when a field it references is clicked into. Therefore, two fields that reference each other should only re-calculate one at a time when the other is changed manually.
Maybe those assumptions above are wrong, but any rate, it’s not allowed. But perhaps there is a workaround with an additional (hidden) field or two that can do some IF() or UPDATEIF() trickery? I am trying to avoid having separate fields for calculated results and manual entries.