I am getting #circularreference and I am not sure how to fix it.

TracyLAshton

I am trying to get this calculation to work

=IF(TODAY() < [Start]@row, "not started", IF(AND(TODAY() >= [Start]@row, TODAY() <= [Finish Date]@row, [% Complete]@row < 100), "In progress - on track", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 100), "in progress - delayed", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row = 100), "complete", IF(AND(TODAY() > [Start]@row, [% Complete]@row = 0), "postponed", [Project Progress]@row)))))

I am trying to Update the [Project Progress] with "In progress - on track" when today is between [Start]and [Finish Date] and [% Complete]is less than 100%, update the [Project Progress] with "in progress - delayed" when today is beyond [Finish Date]and the [% Complete] is less than 100%, update the [Project Progress] with "complete" if today is past [Finish Date] and the [% Complete] is 100%, update [Project Progress] to "not started" if today is before [Start], update [Project Progress] to "postponed" if today is past [Start]and [% Complete]is 0%

Can anyone help me please and thanks. Even the AI option gives me the same calculation

DKazatsky2

Hi @TracyLAshton,

You are getting the circular reference because you have "[Project Progress]@row" in the last part of the formula - you can not reference the column housing the formula. What happens is you simply remove that?

=IF(TODAY() < Start@row, "not started", IF(AND(TODAY() >= Start@row, TODAY() <= [Finish Date]@row, [% Complete]@row < 100), "In progress - on track", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 100), "in progress - delayed", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row = 100), "complete", IF(AND(TODAY() > Start@row, [% Complete]@row = 0), "postponed")))))

Hope this helps,

Dave

=Chris Palmer

Hi @TracyLAshton,

Here is how I would handle such a scenario. First we need to identify the root cause.

Step 1. Note the name of the column of the formula you listed starting with =IF(TODAY.

Step 2. Breakdown each reference to another column from your =IF(TODAY formula like so:
[Start]
[Finish Date]
[% Complete]
[Project Progress]

Step 3. If the column name of your =IF(TODAY Formula in Step 1 is referenced inside the columns of Step 2, this is triggering your circular reference. If the column name is not found, then you will need to continue to drill down, the formulas deeper within Step 2 to see if the column name in Step 1 is found. Continue this process to identify the circular reference.

From here we can come up with an improvement to your initial formula to eliminate the error.

Let me know if you need further assistance.

DKazatsky2

Hi @TracyLAshton,

You are getting the circular reference because you have "[Project Progress]@row" in the last part of the formula - you can not reference the column housing the formula. What happens is you simply remove that?

=IF(TODAY() < Start@row, "not started", IF(AND(TODAY() >= Start@row, TODAY() <= [Finish Date]@row, [% Complete]@row < 100), "In progress - on track", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 100), "in progress - delayed", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row = 100), "complete", IF(AND(TODAY() > Start@row, [% Complete]@row = 0), "postponed")))))

Hope this helps,

Dave

TracyLAshton

You all are a complete genius. Thank you so very much for your help. I have so much to learn.

thanks again

Tracy