Formula to find latest/most current

Mike Meyer

Hey friends! 

I have two sheets. I need one to behave as a "log" (columns: Machine SN (Primary), Machine Hours, Machine Status (Dropdown List), Current Machine Location). This log will be populated by Forms. The second, separate sheet will be a "ledger" where each row will represent a different, individual machine (Machine SN) and the columns need to read the most current info from the columns in the log.

Is there a formula that I can have in the "Ledger" that will read the info in the columns in the "log" and return the latest/most current entries?

Thank you for your help!

Mike Meyer

Answered by colleague: Change your form input into the Log sheet so it puts the newest submission at the top of the sheet, instead of the bottom. Then in your ledger in machine status column you could just use a simple index collect formula like: =INDEX(COLLECT({MachineStatus}, {MachineID}, [Machine ID#]@row),1)

{MachineStatus} would reference your Machine Status column in the Log

{MachineID} would reference the customer machine id column in the Log.

By having your form feed in from the top + using that index collect formula, ending with the # 1, it will return the matching criteria closest to the top.

This worked!

David Tutwiler

Will your Machine SN be unique in the log or will there be multiple entries in the log for the same machine? If it's unique, then this will be fairly easy. For any of your columns on the ledger you could write a formula with a cross sheet reference that looks for a Machine SN match and pulls in the data you want. Something like:

=INDEX({log}, MATCH([Machine SN]@row, {log_machineSN}, 0), 2)

In this formula {log} is a cross sheet reference that references the whole range from Machine SN to the last column you need to reference and {log_machineSN} is a cross sheet reference that references on the the Machine SN column on the log sheet.

The formula looks for a match where the Machine SN matches on the ledger and the log and then returns column number 2 from the {log} range. You can change the column number to return the various columns of information you're looking for.

I hope this helps.

Mike Meyer

Hey @David Talbert@David Tutwiler!

Thank you for looking into this for me!

The Machine SN is the unique identifier but there will be multiple entries for each Machine SN in the log as the data in the columns (Machine Hours, Machine Status, Current Machine Location) changes.

Maybe this visual will help. Thanks again!

David Tutwiler

I can't think of a good/consistent way in which formulas would make this work. Have you looked into Data Mesh? It seems like that would be able to handle this particular problem set.

https://www.smartsheet.com/marketplace/premium-apps/datamesh

Mike Meyer

Answered by colleague: Change your form input into the Log sheet so it puts the newest submission at the top of the sheet, instead of the bottom. Then in your ledger in machine status column you could just use a simple index collect formula like: =INDEX(COLLECT({MachineStatus}, {MachineID}, [Machine ID#]@row),1)

{MachineStatus} would reference your Machine Status column in the Log

{MachineID} would reference the customer machine id column in the Log.

By having your form feed in from the top + using that index collect formula, ending with the # 1, it will return the matching criteria closest to the top.

This worked!