Can a formula be entered into a date field and get it to stick
I have a sheet that tracks consultations. These are normally each row represents a single consultation. But occasionally we have a workshop where it takes several row to capture the consultations. We are using child / parent relationships to capture the rows related to the workshop. We use the top row as the parent and indent the corresponding rows beneath it to make them child rows.
The parent / child rows are identical as far as columns and the information captured. However, once a row becomes a child row we would like the date fields to be set to the dates in the parent row. I am able to do that by putting this formula in the date fields …
=IF(CRow@row = 1, PARENT(FromDate@row))
=IF(CRow@row = 1, PARENT(ToDate@row))
=IF(CRow@row = 1, PARENT(CC2Date@row))
The CRow is a a checkbox field that is checked once the row is indented to make it a child.
My issue is that I cannot get the formulas to stick. What I mean is I can put these formulas in all the associated date fields for the entire table. Since most rows are not going to be child rows the normal action is for someone to enter a date for each of the 3 date fields on the record. If they do this, then the formula is no longer included in that date field. So if later it is indented to become a child row there is no formula to act on. Or if someone enters a date and then removes a date in the normal field the formula is also removed. Is there a way I can do this and make the formulas persistent if the date is removed?
Any ideas to help me with this. I have tried a lot of things and work-arounds but none see to work.
Help Article Resources
Check out the Formula Handbook template!