What Formula should I use?

brandi.meyer80376 · 12/09/20

Good afternoon,

I am working on sheet where I want to display the most recent date data has been entered for a specific area. For example; I have column that shows which area is being reported on and a column for the week the information associated was filled in. I would like to capture in a "control column" when the last update was for each area so I can use the information to fill in another sheet.

The sheet is either filled in via a form, or resources input their new data on a new empty row.

Thanks in advance,

B.

Debbie Sawyer · 12/09/20

There are system dates you can use, which store the date the row is created and the date the row is last modified. These are found in the Column settings under Auto-Number/System as shown in the screen capture below.

OR

At SBP we have a suite of apps that can enhance some functionality of a sheet, these are our SmarterControls4Smartsheet. Within that suite of apps we have one called Cell Level Timestamp Manager, which will allow you to report on the last modified date/time on a nominated cell. See our website for more on this:

https://www.smarterbusinessprocesses.com/smartercontrols4smartsheet-cell-level-timestamp-manage/

Hopefully one of these suggestions might be what you are looking for!

Kind regards

Debbie

brandi.meyer80376 · 12/09/20

I see what you're saying, all great solutions and ideas. But what I am trying to is marry the information back to a project sheet for every individual area being reported on.

Debbie Sawyer · 12/09/20

Can you just use a cell link?

So the date cell on the project sheet is "linked" to the data in the other sheet?

Would that work?

Or you could use the Index() nested with a Match()?

Mark Cronk · 12/09/20

Hi @brandi.meyer80376 ,

Sounds like you need to use COLLECT. The syntax is: COLLECT( range criterion_range1 criterion1 [ criterion_range2criterion2... ])

Your formula would be -- substitute my column names for the right ones on your sheet:

=MAX(COLLECT( [update date]:[update date], [Area]:[[Area], ="Area B")

This formula needs to be in a column formatted as date. If you build a 2 column table, you could put the Area name in the left column and the latest update in the right. Then rather than typing an area in each formula you can reference the area name column. Instead of [Area]:[[Area], ="Area B" you could use [Area]:[[Area], =[Area Name]@Rowan Cory Work?

Mark