Problem retaining formulas in cells that are in a form

Diane Moore

I have an intake log that has a form for parents of child patients to complete in order to apply for services. I have added logic that allows the user to complete the patient's address. Then, I ask the question of "Is your address the same as the patient's" (Yes/No).

If yes, I want the parent's address fields to populate with the address provided for the patient.

If no, the person is then prompted to complete their own address.

I then populate each of the address attribute fields with logic like below:

=IF([GUARD1_ADR_SAME]@row = "Yes", [PATIENT_ADR1]@row)

Since I cannot put column calculated fields on a form, I make sure that the calculation is in at least two rows.

Here is the issue. If an applicant says No, it overwrites the cell formulas in that row with what the applicant wrote in the form. Then if the next applicant says YES, the formula is now not working (since it does not exist in at least two rows.

I can think of some hacks for this that would include me having a bunch of helper columns that are not overwritten by form fields and then use those helper columns to send into the document generated (for Docusign). But I am hoping someone has a better idea.

Thanks in advance.

Kelly Moore

Hey @Diane Moore

As you have demonstrated, you cannot have a column that contains both a formula and manual entry. Currently there is not automation that allows you to designate a value from one cell to be inserted in another, so yes, you will need one helper column that is the final address and this is what Docusign should point to.

The final address helper column has this formula (I didn't know the name of your 'No' address column)

=IF([GUARD1_ADR_SAME]@row = "Yes", [PATIENT_ADR1]@row,[your No address column]@row)

At least it is only one helper column.

Will this work for you?

Kelly

Kelly Moore

Hey @Diane Moore

As you have demonstrated, you cannot have a column that contains both a formula and manual entry. Currently there is not automation that allows you to designate a value from one cell to be inserted in another, so yes, you will need one helper column that is the final address and this is what Docusign should point to.

The final address helper column has this formula (I didn't know the name of your 'No' address column)

=IF([GUARD1_ADR_SAME]@row = "Yes", [PATIENT_ADR1]@row,[your No address column]@row)

At least it is only one helper column.

Will this work for you?

Kelly

Diane Moore

@Kelly Moore - Thanks for this response. I was afraid that this might be the answer. Unfortunately, I have three sets of addresses and each is broken up into ADR1, ADR2, CITY, STATE, ZIP to ensure that they are fed to the Docusign contract in the correct fields. I went ahead and implemented the extra helper columns this weekend and it definitely fixed the problem. Unfortunately, no matter how hard I try, helper columns are going to be the death of me given the sheet size constraints so they are something I try to avoid.