Grab Last Value in Row (Left to Right)

Josh ciaramitaro

Hello, I am looking to grab the last value entered into a row. I have seen many examples of this using INDEX and COLLECT but I am not understanding the syntax. Can someone please assist? The screenshot below shows the outcome I am looking for. Thanks in advance!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318615#Comment_318615

Glad it worked. Getting it to work with one set of formulas is tough when your data progresses column to column like this. It would be a lot easier if you transposed everything, so that for each section you had rows for the months, and columns for "actual", "plan", and "CM".

** OK as I was composing this answer, I thought of something else, and I can't believe I didn't think of it yesterday. 😵 **

We can do this with two easy formulas IF you make rows called "actual", "plan", and "CM" into child rows of the rows in gray shading, or put your Actual YTD value on the same row as "actual".

To create child rows, select the three cells for actual, plan, and CM under a gray row, and hit the indent button:

Now the gray row is parent to the three child rows:

We can get rid of your Month and Value helper columns, we won't need them.

In the Count helper column, on the row for "actual," enter the following:

=VALUE(RIGHT(JOIN(Jan@row:Dec@row), 1))

This is going to find the latest month's entry and present it to you as a number. The JOIN part combines all the number values in the actual row into a string, so if Jan Feb and March are 8, 6, and 4, it creates a text string, "864". The RIGHT function tells it to find the first character on the right side of the string, in this case "4". Since we pulled that 4 from a string value, we need to convert it back to a number value, and that's where the VALUE function comes in. The end result is a numeric 4 in the Count column for the row.

Back over in the "Actual YTD" column on the gray row, use the formula:

=MAX(CHILDREN(Count@row))

Since we only have a value in the Count column for the "actual" row, that's obviously the max value among the three child rows, so it finds the number 4.

IF YOU DON'T WANT TO USE PARENT/CHILD ROWS:

Your Actual YTD value would need to be on the same row as "actual." Then just use the

=VALUE(RIGHT(JOIN(Jan@row:Dec@row), 1))

formula directly in the "Actual YTD" column on the "actual" row.

Jeff Reisman

@Josh ciaramitaro

Let me see if I understand what you're trying to do. You want the last value entered on row 3, whether it's entered in the January column, February column, etc, to populate the Actual YTD column on Row 2?

Are these numbers expected to be higher each month than the preceding month? So if January is 4, February may be 7, March likely around 11, etc?

Josh ciaramitaro

Hey Jeff!

Thanks for the reply, the number is intended to go down so MAX wouldn't work for me. We aren't summing a value either just want to grab the last value entered into the month column. But other than that yes you have the model correct.

Jeff Reisman

Ok you'll need a couple of helper columns to do this. (These can be hidden after you get them working.)

First helper column: "Count". On the same row where you are putting you monthly numbers, row 3:

=COUNT(Jan@row:Dec@row)

This will count the number of months that have values filled in across the row.

Then add two more helper columns, "Month" and "Value"

In the month column, just enter your month numbers: 1, 2, 3, 4.. up to 12.

In the Value column, in the row corresponding to each month number:

=Jan3 (this will put the value for your "Jan" column, row 3, in this cell.)

=Feb3

=Mar3

etc.

up to =Dec3 in the column for month 12.

Back up in your [Actual YTD]2 cell, enter the following:

=INDEX(Value:Value, MATCH(Count3, Month:Month, 0))

So, in a nutshell, the Count3 cell contains the number for the latest month that was filled in, because if you have values for Jan, Feb, and March, it counts 3 values, and 3 corresponds to March.

The index formula looks at the values for each month that are in your helper columns, and finds the value for the month that equals the number in Count3.

Josh ciaramitaro

This worked! The only issue I foresee is I need this same formula many different times in the same sheet, see screenshot:

Is this my only option, or is there something easier that can be replicated for all the individual rows that will need this formula?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318615#Comment_318615

Glad it worked. Getting it to work with one set of formulas is tough when your data progresses column to column like this. It would be a lot easier if you transposed everything, so that for each section you had rows for the months, and columns for "actual", "plan", and "CM".

** OK as I was composing this answer, I thought of something else, and I can't believe I didn't think of it yesterday. 😵 **

We can do this with two easy formulas IF you make rows called "actual", "plan", and "CM" into child rows of the rows in gray shading, or put your Actual YTD value on the same row as "actual".

To create child rows, select the three cells for actual, plan, and CM under a gray row, and hit the indent button:

Now the gray row is parent to the three child rows:

We can get rid of your Month and Value helper columns, we won't need them.

In the Count helper column, on the row for "actual," enter the following:

=VALUE(RIGHT(JOIN(Jan@row:Dec@row), 1))

This is going to find the latest month's entry and present it to you as a number. The JOIN part combines all the number values in the actual row into a string, so if Jan Feb and March are 8, 6, and 4, it creates a text string, "864". The RIGHT function tells it to find the first character on the right side of the string, in this case "4". Since we pulled that 4 from a string value, we need to convert it back to a number value, and that's where the VALUE function comes in. The end result is a numeric 4 in the Count column for the row.

Back over in the "Actual YTD" column on the gray row, use the formula:

=MAX(CHILDREN(Count@row))

Since we only have a value in the Count column for the "actual" row, that's obviously the max value among the three child rows, so it finds the number 4.

IF YOU DON'T WANT TO USE PARENT/CHILD ROWS:

Your Actual YTD value would need to be on the same row as "actual." Then just use the

=VALUE(RIGHT(JOIN(Jan@row:Dec@row), 1))

formula directly in the "Actual YTD" column on the "actual" row.

Josh ciaramitaro

Awesome work on this Jeff!! Thanks a bunch for all the effort put in here! I took your syntax of:

=VALUE(RIGHT(JOIN(Jan@row:Dec@row), 1))

And replaced it with:

=VALUE(RIGHT(JOIN(Jan3:Dec3), 1))

Worked perfectly! I will just need to monitor for the double-digit entries and adjust accordingly.

Again, thank you for the tremendous effort and thought on this!