Get the value with the latest Date
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?
Thanks in advance.
Best Answers
-
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:
Cheers!
-
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
-
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))
Answers
-
@Andrée Starå @Paul Newcome , Please help me with this formula. Thank you very much.
-
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:
Cheers!
-
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
-
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?
-
Hi Genevieve, thank you for this solution. Can you help me to write formula when there are 2 source sheet?
-
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))
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
