Return the last entries of a row in the first row

luizrocha · 01/14/22

I need that the first row always receive the information on the last row.

Please can someone help me with that.

Genevieve P. · 01/17/22

I would suggest adding a System Generated Auto-Number column to the sheet.See: Auto-Number Rows

Then you can use this in an INDEX Function to find the MAX (or newest) row and bring that data back into the top row, like so:

=INDEX(Solution:Solution, MAX([Row ID]:[Row ID]))

You just need to swap out the column at the front of the Index function to reference the next one:

=INDEX(Status:Status, MAX([Row ID]:[Row ID]))

Screen Shot 2022-01-17 at 1.43.18 PM.png

Cheers,

Genevieve

Genevieve P. · 02/16/22

Hi @luizrocha

Since your current row doesn't say "Approved" or not, we'll want to change your IF statement to say IF this formula = "Approved", then say "Approved", otherwise find the One Row Above MAX. Does that make sense?

Try this:

=IF(INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID])) = "Approved", "Approved", INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]) - 1)

Notice that I have the -1 outside of the MAX function. This is because I want to find the MAX row, but then remove one after this is found:

MAX([Row ID]:[Row ID]) - 1

Cheers,

Genevieve

Genevieve P. · 01/17/22

Hi @luizrocha

I would suggest adding a System Generated Auto-Number column to the sheet.See: Auto-Number Rows

Then you can use this in an INDEX Function to find the MAX (or newest) row and bring that data back into the top row, like so:

=INDEX(Solution:Solution, MAX([Row ID]:[Row ID]))

You just need to swap out the column at the front of the Index function to reference the next one:

=INDEX(Status:Status, MAX([Row ID]:[Row ID]))

Cheers,

Genevieve

luizrocha · 02/16/22

https://community.smartsheet.com/discussion/comment/316374#Comment_316374

Hi Genevieve,

Please can you help me to consider this condition.

I've tried using this formula

=IF([Luiz Chaves approval]@row = "Approved", INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID])), INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]-1))

However it doesnt work.

I want to take the last line (7) when is Approved the Status and in case that is not that Approved then consider the line (6).

Thank you,

Genevieve P. · 02/16/22

Hi @luizrocha

Since your current row doesn't say "Approved" or not, we'll want to change your IF statement to say IF this formula = "Approved", then say "Approved", otherwise find the One Row Above MAX. Does that make sense?

Try this:

=IF(INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID])) = "Approved", "Approved", INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]) - 1)

Notice that I have the -1 outside of the MAX function. This is because I want to find the MAX row, but then remove one after this is found:

MAX([Row ID]:[Row ID]) - 1

Cheers,

Genevieve

luizrocha · 02/17/22

https://community.smartsheet.com/discussion/comment/319780#Comment_319780

Hi Genevieve,

I dont know why is not taking this value because this last lane is already Approved.

Thank you so much for your help.

Genevieve P. · 02/17/22

Hi @luizrocha

Perhaps I've misunderstood - what cell are you putting the formula in to?

Are you putting the formula into Row 1 under the "Luiz Chaves Approved" column?

luizrocha · 02/17/22

https://community.smartsheet.com/discussion/comment/319970#Comment_319970

Hi Genevieve,

Sorry for bugging you however I think I'm missing something in my formulas:

Status is Approved so it should take the last one (line 10) instead of the next to the last one (line 9). And look how it is working:

On this occasion here is working fine when "Luiz Chaves approval" is Submitted, look:

(Status is Submitted so it's gonna take the next to the last one, line 10)

The problem is when I approve on the email the form:

And should take the the last line (line 11) to the first line how ever is not working.

Also after a refresh:

Thank you,

Genevieve P. · 02/18/22

Hi @luizrocha

Thank you for these screen captures!

The formula in your first screen capture is looking in it's own column for the status "Approved", however this is a Symbol column so it will never have the word "Approved" in it, which is why it's always taking the second cell.

You'll want to change the column it's looking into for "Approved".

If this is TRUE, complete the normal INDEX(MAX. If it's FALSE, then complete the INDEX(MAX - 1, like so:

=IF(INDEX([Luiz Chaves Approved]:[Luiz Chaves Approved], MAX([Row ID]:[Row ID])) = "Approved", INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]), INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]) - 1)

Let me know if this updated formula works!

Cheers,

Genevieve

luizrocha · 02/18/22

https://community.smartsheet.com/discussion/comment/320121#Comment_320121

Thank you Genevieve, but for some reason it says this:

Regards

Genevieve P. · 02/22/22

My apologies, @luizrocha! I missed an extra closing ) after the first MAX, before the next INDEX.

=IF(INDEX([Luiz Chaves Approved]:[Luiz Chaves Approved], MAX([Row ID]:[Row ID])) = "Approved", INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID])), INDEX([Current Status]:[Current Status], MAX([Row ID]:[Row ID]) - 1))

Try adding that closing ) back in.

Return the last entries of a row in the first row

Best Answers

Answers

Help Article Resources

Categories