Getting information from the latest line entry based on date
Hi,
I'm setting up a page where you enter 5S audit test data from a Form. It puts a new row in every time with the 5 different 5S scores each in it's own column. We need to find a way to pull the data from the most recent new row.
I need a way of identifying the row with the most recent date and then moving across X many rows to pull the score from the correct column.
It seems like I need to use the MAX function and maybe VLOOKUP, but I've had no luck getting anything to work.
Can anyone point me in the right direction?
Thanks
Comments
-
is the x many rows always the same number, or does it vary based on other criteria? If it varies, what is the criteria?
-
I would suggest adding in a Created (date) type column.
Then you can use
=INDEX([Score Column]:[Score Column], MATCH(MAX(Created:Created), Created:Created, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
We've got 6 different numbers, in 6 different columns on each row.
-
See my suggestion below. Since you can specify single columns in an INDEX function, you can focus solely on the Created column to find the most recent and the score column you are trying to pull from.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Do you have the audit cycle number included on your sheet? you could reference that with your max. Otherwise you can use the previously suggested created column.
How do you know which column to grab? Is it always the 6th column over?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!