Formula that automatically updates status column

LaDonna

I have a project plan that I'd like to automatically update/change the status cell based on count if results for not started, in progress, and complete task counts. The formula I have so far is:

=IF(AND([Notes22]@row > 0, [Notes23]@row = 0, [Notes24]@row = 0), "Complete", IF([Notes23]@row > 0, "In Progress", "Not Started"))

The data type in the columns (Notes22 is Complete Count, Notes23 is In Progress Count, and Notes24 is Not Started Count) are the result of formulas (not numeric values directly inputted), could this be causing the issue? Please help!

Genevieve P.

Hi @LaDonna

The "@row" reference tells the formula to look in the same row, however your formula wants to look at rows 22, 23, and 24 in the Notes column.

Try the exact same formula as in your screen capture but without any @row

=IF(AND(Notes22 > 0, Notes23 = 0, Notes24 = 0), "Complete", IF(Notes23 > 0, "In Progress", "Not Started"))

dojones

It's possible your formula is returning text instead of a value because of the way a formula is written. Try wrapping the Notes22, 23, 24 formulas in Value() to see if it helps.

LaDonna

Thank you for responding. Unfortunately, it's still not working I've tried:

=IF(AND(Notes22@row > 0, Notes23@row = 0, Notes24@row = 0), "Complete", IF(Notes23@row > 0, "In Progress", "Not Started"))

=IF(AND(Notes22@row > 0, Notes23@row = 0, Notes24@row = 0), "Complete", IF(Notes23@row > 0, "In Progress", "Not Started"))

Genevieve P.

Hi @LaDonna

I agree that it might be because of how the values are appearing in your [Notes22] columns.

You first formula should work. In order to help we'll need to know the following:

• What "is not working"? Are you getting an error or an incorrect result?
• What are the formulas in the cells you're referencing?
• Can you list out in bullet points when you want each status to occur?

It would be most helpful to see a screen capture of your sheet with the formula open, showing column headers, but please block out sensitive data!

Thanks,
Genevieve

LaDonna

Hi,

And thank you for replying. I'm receiving a #unparseable error for the formula. The formula:

Referencing:

I'd like the formula to accurately record the status of the project.

If Notes 22 and 23 = 0 and Notes 24 > 0, the status will be "Not Started".

• If Notes 22 > 0 or Notes 23 > 0, the status will be "In Progress".

• If Notes 22 > 0, and both Notes 23 and Notes 24 = 0, the status will be "Complete".

I've also tested:

=IF(AND(Notes22@row = 0, Notes23@row = 0, Notes24@row = 0, ), "Not Started", IF(OR(Notes22@row > 0, Notes23@row > 0 ), "In Progress", IF(AND(Notes22@row > 0, Notes23@row = 0, Notes24@row = 0 ), "Complete", "")))@row = 0,

Genevieve P.

Hi @LaDonna

The "@row" reference tells the formula to look in the same row, however your formula wants to look at rows 22, 23, and 24 in the Notes column.

Try the exact same formula as in your screen capture but without any @row

=IF(AND(Notes22 > 0, Notes23 = 0, Notes24 = 0), "Complete", IF(Notes23 > 0, "In Progress", "Not Started"))

LaDonna

That worked, thank you very much. It's the little things!