Get the value with the latest Date

Benn
Benn ✭✭
in Formulas and Functions

Hi Experts,

I need help in getting the value from the item column of Test Sheet1 which has the latest date and put it in the test sheet 2.

I am trying to use, join(collect(Item sheet1 , Date, max(Date(@cell)) but not working.

Can you help me with the correct formula? And second question, what if I have 2 reference sheet (Sample Test sheet 3). How to adjust the formula?

image.png
image.png

Thanks in advance.

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, @Benn

    Give this a try...

    =JOIN(COLLECT({Sheet1 Range is Item}, {Sheet1 Range is ID}, =ID@row, {Sheet1 Range is Date}, =MAX(COLLECT({Sheet1 Range is Date}, {Sheet1 Range is ID}, =ID@row))), ",")

    Cross Sheet References that you'll need to define:

    • {Sheet1 Range is Item} is the column, Item, on Test Sheet1.
    • {Sheet1 Range is ID} is the column, ID, on Test Sheet1.
    • {Sheet1 Range is Date} is the column, Date, on Test Sheet1.

    The Smartsheet functions used in the formula are: JOIN(), COLLECT(), MAX(). You can find documentation for these and other functions at:

    https://help.smartsheet.com/functions


    Cheers!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Benn

    It sounds like you're on the right track!

    Try this formula structure:

    =INDEX(COLLECT({Item Column}, {ID Column}, ID@row), MATCH(MAX(COLLECT({Date Column}, {ID Column}, ID@row)), {Date Column}), 0)


    Essentially this is saying the following:

    Gather together all the items associated with this row's ID, then find the row that has the MAX date (specific to this row's ID) and only display the item that matches this Max date.

    Let me know if it works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to want to write out that same formula except have it pointing to the second sheet. You would then use an IF statement to say that if the greatest date is on sheet two, then use the sheet two formula, otherwise use the sheet three formula. It is going to look something like this...


    =IF(MAX(MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), MAX(COLLECT(Sheet 3 {Date Column}, {Sheet 3 ID Column}, ID@row))) = MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), INDEX(COLLECT({Sheet 2 Item Column}, {Sheet 2 ID Column}, ID@row), MATCH(MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), {Sheet 2 Date Column}), 0), INDEX(COLLECT({Sheet 3 Item Column}, {Sheet 3 ID Column}, ID@row), MATCH(MAX(COLLECT({Sheet 3 Date Column}, {Sheet 3 ID Column}, ID@row)), {Sheet 3 Date Column}), 0))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

  • Benn
    Benn ✭✭

    @Andrée Starå @Paul Newcome , Please help me with this formula. Thank you very much.

    · Share on FacebookShare on Twitter
  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, @Benn

    Give this a try...

    =JOIN(COLLECT({Sheet1 Range is Item}, {Sheet1 Range is ID}, =ID@row, {Sheet1 Range is Date}, =MAX(COLLECT({Sheet1 Range is Date}, {Sheet1 Range is ID}, =ID@row))), ",")

    Cross Sheet References that you'll need to define:

    • {Sheet1 Range is Item} is the column, Item, on Test Sheet1.
    • {Sheet1 Range is ID} is the column, ID, on Test Sheet1.
    • {Sheet1 Range is Date} is the column, Date, on Test Sheet1.

    The Smartsheet functions used in the formula are: JOIN(), COLLECT(), MAX(). You can find documentation for these and other functions at:

    https://help.smartsheet.com/functions


    Cheers!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Benn

    It sounds like you're on the right track!

    Try this formula structure:

    =INDEX(COLLECT({Item Column}, {ID Column}, ID@row), MATCH(MAX(COLLECT({Date Column}, {ID Column}, ID@row)), {Date Column}), 0)


    Essentially this is saying the following:

    Gather together all the items associated with this row's ID, then find the row that has the MAX date (specific to this row's ID) and only display the item that matches this Max date.

    Let me know if it works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Benn
    Benn ✭✭
    https://community.smartsheet.com/discussion/comment/298477#Comment_298477

    Hi Toufong, Thank you very much. What if I have 2 source sheet, Like we have sheet 3 with different dates and items but still from those 2 source sheet I need to get the item with the latest date. How to write or combine formulas with 2 source sheet?

    · Share on FacebookShare on Twitter
  • Benn
    Benn ✭✭
    https://community.smartsheet.com/discussion/comment/298479#Comment_298479

    Hi Genevieve, thank you for this solution. Can you help me to write formula when there are 2 source sheet?

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to want to write out that same formula except have it pointing to the second sheet. You would then use an IF statement to say that if the greatest date is on sheet two, then use the sheet two formula, otherwise use the sheet three formula. It is going to look something like this...


    =IF(MAX(MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), MAX(COLLECT(Sheet 3 {Date Column}, {Sheet 3 ID Column}, ID@row))) = MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), INDEX(COLLECT({Sheet 2 Item Column}, {Sheet 2 ID Column}, ID@row), MATCH(MAX(COLLECT({Sheet 2 Date Column}, {Sheet 2 ID Column}, ID@row)), {Sheet 2 Date Column}), 0), INDEX(COLLECT({Sheet 3 Item Column}, {Sheet 3 ID Column}, ID@row), MATCH(MAX(COLLECT({Sheet 3 Date Column}, {Sheet 3 ID Column}, ID@row)), {Sheet 3 Date Column}), 0))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Benn
    Benn ✭✭
    https://community.smartsheet.com/discussion/comment/298525#Comment_298525

    Hi Paul, I tried to use the formula to the actual data sheet and it prompts me a #INVALID DATA TYPE, This is the formula I patterned to your formula,

    =IF(MAX(MAX(COLLECT({Nurse's Daily Schedule 2021 - CA Sched Visit}, {Nurse's Daily Schedule 2021 - CA Patient ID}, [Patient ID]@row)), MAX(COLLECT({Nurse's Daily Schedule 2021 - CA - New SchedVisit}, {Nurse's Daily Schedule 2021 - CA - New Patient ID}, [Patient ID]@row))) = MAX(COLLECT({Nurse's Daily Schedule 2021 - CA Sched Visit}, {Nurse's Daily Schedule 2021 - CA Patient ID}, [Patient ID]@row)), INDEX(COLLECT({Nurse's Daily Schedule 2021 - CA Visit Status}, {Nurse's Daily Schedule 2021 - CA Patient ID}, [Patient ID]@row), MATCH(MAX(COLLECT({Nurse's Daily Schedule 2021 - CA Sched Visit}, {Nurse's Daily Schedule 2021 - CA Patient ID}, [Patient ID]@row)), {Nurse's Daily Schedule 2021 - CA Sched Visit}), 0), INDEX(COLLECT({Nurse's Daily Schedule 2021 - CA - Visit Status}, {Nurse's Daily Schedule 2021 - CA - New Patient ID}, [Patient ID]@row), MATCH(MAX(COLLECT({Nurse's Daily Schedule 2021 - CA - New SchedVisit}, {Nurse's Daily Schedule 2021 - CA - New Patient ID}, [Patient ID]@row)), {Nurse's Daily Schedule 2021 - CA - New SchedVisit}), 0))

    Using simple INDEX(COLLECT pointing into one sheet prompted me a #INVALID VALUE. Please advise what to fix. Thanks.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!