FORMULA - Pulling Data from last row filled

Laura
Laura ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

H E L P   P L E A S E

I have 5 rows (children) of possible data input areas. All 5 rows may not always be used.

In Column "M" a number of 1,2 or 3 will be entered. Based on this number, Column "A" of the parent row should reflect

"Closed" if the last row containing data is 1

"Work in Progress" if the last row containing data is 2 or 3

or "Not Started" if all rows are blank. 

 

How can I write this formula easily, to state this need? 

I'm having trouble figuring out how to pull data from the last populated row when all rows may not be used.

 

NOTE ** Column  M is a drop down column, restricted to values 1, 2 or 3 only. 

Currently, if blank formula below is even giving me an invalid operation error.

=IF(ColumnM14:ColumnM18 = "", "Not Started"

STATUS_FORMULA.jpg

Comments

  • Laura
    Laura ✭✭✭✭✭✭

    This formula only works if I remover the IF(ColumnM14:Column18,"") "Not Started"

    =IF(OR(COUNTIF(ColumnM14:ColumnM18, "3") > 0, COUNTIF(ColumnM14:ColumnM18, "2") > 0), "Work In Progress", IF(ColumnM14:ColumnM18, "") "Not Started", "Closed")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!