What Formula should I use?
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.
Answers
-
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:
Hopefully one of these suggestions might be what you are looking for!
Kind regards
Debbie
-
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.
-
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()?
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I think you have to delete/edit your post because the feature (record a date) you're referencing is under NDA.
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks @Andrée Starå I didn't realise. I'll try now
-
Happy to help! Easy to miss!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I am going to second @Mark Cronk's suggestion of the MAX/COLLECT to pull the most recent auto-date/time stamp that @Debbie Sawyer suggested.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!