How do I get a cell to display the value of the last cell in a column excluding one of the values?

jromano

Hi All,

I have a column in a project plan with a droplist of certain values. I want to display the value of the last cell in the column that has any value except Not Started.

Would someone please provide a suggestion?

jromano

Got it! I had to put [ ] around each reference to the column name!

AdamApexConsultants

You can use a combination of INDEX, COLLECT, and COUNTIF to find the last non-"Not Started" value in a dropdown column. Here’s a formula approach that should work:

=INDEX(COLLECT(Project Status:Project Status, Project Status:Project Status, <> "Not Started"), COUNTIF(Project Status:Project Status, <> "Not Started"))

jromano

Hi Adam

I tried it and it came back #UNPARSEABLE

jromano

One thing that might be important: the values live in child rows to parent rows in the project plan

jromano

Got it! I had to put [ ] around each reference to the column name!