Why is my formula UNPARSEABLE? Is it because of Dropdown Dependency?

twaiteORELD

It's a long formula - I want to make the number of starts in the "Current Status" column dependent upon the selection in the drop down from the "Contract/Grant Status" column:

=IF([Contract/Grant Status:]1 = Submitted, "Empty", IF([Contract/Grant Status:]1 = Assigned, "One", IF([Contract/Grant Status:]1 = ELD Review ODE Draft, "Two", IF([Contract/Grant Status:]1 = To DOJ, "Three", IF([Contract/Grant Status:]1 = Out for Signatures, "Four", IF([Contract/Grant Status:]1 = Executed, "Five" ))))))

Thoughts anyone?  Thanks!  Teresa

Nic Larsen

I think you needed quotes around your status indicators. This worked for me, although I had to retype versus copy/paste:

=IF([Contract/Grant Status:]@row = "Submitted", "Empty", IF([Contract/Grant Status:]@row = "Assigned", "One", IF([Contract/Grant Status:]@row = "ELD Review ODE", "Two", IF([Contract/Grant Status:]@row = "To DOJ", "Three", IF([Contract/Grant Status:]@row = "Out for Signatures", "Four", IF([Contract/Grant Status:]@row = "Executed", "Five"))))))

twaiteORELD

That fixed it - except for one thing.  In a group of children, the first one doesn't show anything in the cell, but the cells below work perfectly.  Thoughts?

 

Nic Larsen

Do you mean it wasn't working on a Parent row? Can you attach a screenshot?

twaiteORELD

Actually - I figured it out - it was drawing from the row above and I needed to fix it.

Nic Larsen

Excellent. 

Paul Newcome

A tip to help avoid that would be to use the @row function when a formula is referring to a cell on the same row. So if the formula is on row one and referencing other cells in row one, you could use

 

[Column Name]@row

 

This helps avoid having the formula accidentally referencing the wrong row. Especially when dragfilling.