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

Find more posts tagged with

Forms

Formulas

Comments

L_123

is the x many rows always the same number, or does it vary based on other criteria? If it varies, what is the criteria?

Paul Newcome

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))

Graham Rimell

We've got 6 different numbers, in 6 different columns on each row.

Paul Newcome

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.

L_123

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…