Return the last entries of a row in the first row

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

Please can someone help me with that.

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • 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.
    Genevieve P. Employee Admin

    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?


  • 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.
    Genevieve P. Employee Admin

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!