Allow circular calculations

spauliszyn

There should be an option to allow circular calculations with a limit to circular iterations.

This is my case:

I am making an engineering task tracking sheet. All requested tasks are usually independent but occasionally we get requests for several sequential tasks. I will usually have "Deliverable date" and "Est. Duration" data for new requests. So many tasks will be concurrent as they are allocated to different engineers. Many of these will be tasks down the line that, unless critical or impactful, I do not want to schedule/assign until a month out. (I am not using the resource management feature at this stage.)

There will be an automatically calculated 'hidden' start date and end date column that will be connected to the Gantt chart. I will also provide blank "Est. Start Date" and "Est. End Date" columns. With some formula magic, I could have the following scenarios:

• Both blank: End date will pick the "Deliverable Date" and start date is calculated by that and "Est. Duration".

• "Est. Start Date" is a date: Start date will pick "Est. Start Date" and calculate end date based on "Est. Duration".

• vice versa for "Est. End Date".

• Both dates given: Start and end dates picked from these regardless of duration.

• "Est. Start Date" is a ID# of another task: The start date will be the calculated end date of a different task identified by ID# of that task.

So at some point the actual calculated start date or end date is dependant on each other but never at the same time. Nonetheless a "CIRCULAR REFERENCE" error occurs. So even if al the circular calculations are done with only one iteration, all the formulas will resolve.

The work around for now is I can have temporary start/end date columns that do the intermediate calculations and then occasionally copy&paste the values onto the start/end date columns for the Gantt chart. This is not automated though. There is not even an "Automation" function to copy values from one column to another.