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

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

Find more posts tagged with

Education

Formulas

Government

Support

Conditional Formatting

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.