Max and index match function

Simran Curry
Simran Curry ✭
in Formulas and Functions

Hi all, i have a sheet where Trailers are logged in multiple time throughout the day and what they had in them such as a PO number or a load number

in a separate sheet i want to type in the Trailer number and i want it to pull the recent entry date of the trailer and the Load number / po that came in that trailer. How can i achive that? if it can pull the time too that will be great.

Tags:
· Share on FacebookShare on Twitter

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    do you want all the information in one column.If so you need the join function. I can build that formula for you but would take a little bit. If you do separate columns and the sheet your pulling from has a created column. I would do a few helper rows. And run index match in each columns. If you provide this information I can figure the best course for you.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter
  • Simran Curry
    Simran Curry ✭

    i want the info in different columns.

    also the sheet where we will be pulling the data from it will have a date column and a time column. it does not have a created column, but i can add it.

    thankyou for replying

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @Simran Curry Ok Last question. Are the Tailor numbers ever used again?. If so you would need some sort of Unique identifier. If not a simple Index match for each column to pull the information you need.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter
  • Simran Curry
    Simran Curry ✭

    yes Trailer numbers are used multiple times . i need the very recent date and time it was logged.

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/436608#Comment_436608

    Ok. Try This out and see how it works for you.

    =Index(Collect({PO ref},{Trailer Number Ref},[Trailor Number Column]@row,{Date Ref},MAX({Date Ref}),{Time Ref},MAX({Time Ref})))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter
  • Simran Curry
    Simran Curry ✭

    i cant seem to get it working.

    any way we can connect and you can show me?

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/20/24

    @Simran Curry

    Absolutely. Do you have teams?

    Also I think I see the issue with the formula I created for you. How ever I know a great work around I use all the time. I can implement for you. Adding a helper column in the sheet that you are pulling your information from. It will have an incremental number based on a few criteria.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · 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!