How to have a 2nd sheet automatically picking up the newest entry from another sheet

Toby_85

Hi Smartsheet Community,

I am at the very early stage of using smartsheet and got stuck, I was hoping someone could help with the below:

Location 1 has a form that captures the status of knives (Knife broken/Knife in working condition/Knife is missing).

The form captures it like this:

I made a 2nd data sheet, which is supposed to capture the status of the knives, turning it into numbers (which want to use for a report to merge various locations, turning it into a dashboard that only shows me the most recent status of the knives)

The idea is that the data sheet only captures the newest entry in the 1st row (the form puts the newest entry on top), in the below case it would be the status of Knife 1 on the first row.

I thought of doing this with +countif, however when a new entry is submitted, Knife 1 on the data sheet refers still to the old entry, not the newest. Using the above screenshot, the data sheet would still refer to RowID 6 / row 2 instead of the newest entry coming on RowID 7 / row 1

=COUNTIF({Location 1 - Knife 1}, "Knife broken")

Thank you already in advance!

Eric Law

@Toby_85 What you can do is use countif/index. You will need to change you range to include the entire sheet, at this point you are just referencing a single line and it seems like the referenced line moves with each input (which makes sense since you don't want your data to change, typically). Try this with the new range.

=COUNTIF(INDEX({Location 1 - Knife 1},1,5),[Primary Location]@row) for column 2. Change the 5 to a 6 for column 3, etc. Hope that works/help.