Difference in cell value between 2 rows based on multiple criteria


Dear Smartsheet, I have a long list of parts based on columns A & B (part & position) with different install dates and hours (column D which is cumulative). I would like to have a colum E (hours on part), which is a difference in operating hours (column D) from date in row to next date when part was installed (ie E7=D8-D7)

The caveat is that also the data is input with other groups so it would not be nicely sorted as there are many parts and positions input randomly, so it needs to look in sheet to same Part/Pos with next date and calculate that delta. It would be ordered by Install date ascending. I also have a Rank column if this helps (ie Rank -1 lookup) or something

Is something like this possible?

Thanks, Paolo


  • Jaykel Torres

    Hey @Paolo Mauro,

    It sounds like you are trying to reposition your Rows while keeping the results of your Formula consistent. This can be done using the Report Feature within Smartsheet; Reports can filter out specific data from a Sheet or Multiple Sheets without disrupting Formulas that rely on cell positioning.

    I hope this helps!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!