I need the latest or last cell value. What formula would be in smartsheet?

Maryjo Reyes

Example:

Description Wk1 Wk2 Wk3 Wk4

Structural 12 18 10 16

Mechanical 2 3 7 -

Electrical 5 1 2 -

Answer should be the latest or last cell value (should be number) input

Structural 16

Mechanical 7

Electrical 2

Stefan

Hi Maryjo,

how many columns "WK..." will you have?

If there are only those 4, then you could use a nested IF formula.

Example where the formula basically works it way back from WK4 to WK1 and checks if the cell is empty:

=IF(ISBLANK([WK4]1), IF(ISBLANK([WK3]1), IF(ISBLANK([WK2]1), IF(ISBLANK([WK1]1), "no data", [WK1]1), [WK2]1), [WK3]1), [WK1]1)

Hope this helps

Stefan

Maryjo Reyes

Hello Stefan,

Thanks for your reply.

I have Week 1 to Week 20 columns.

Paul Newcome

Try somethign along the lines of...

=INDEX([WK1]@row:[WK20]@row, 1, COUNTIFS([WK1]@row:[WK20]@row, @cell <> ""))

Maryjo Reyes

Thanks Paul! Great, It works!

Maryjo Reyes

When the column has no value, It gives #INVALID COLUMN VALUE, is there any way that the cell value will be blank or zero when the WK 1 to WK 20 no cell value or blank? Thank you

Paul Newcome

We can use an IFERROR.

=IFERROR(INDEX([WK1]@row:[WK20]@row, 1, COUNTIFS([WK1]@row:[WK20]@row, @cell <> "")), "")

Maryjo Reyes

Thanks Paul. But It doesn't work.

If I put a cell value "0" (Wk 1 - Wk 20) it will read the last value (Structural)

If no value or blank (Wk 2 - Wk 20) it will not read the last cell value (Mechanical)

If all columns (Wk 1 - Wk 20) has no value, it gives an #INVALID COLUMN VALUE even if I use the IFERROR. (Electrical)

Thank you!

Paul Newcome

I didn't realize you were going to have blanks before the data.

Take a look at THIS THREAD. It should provide a solution for exactly what you are trying to do.