Show Date of Form Submission if criteria are Met
Could not locate a question to begin finding a formula to complete.
I need a cell to display the date a Smartsheet form was submitted based on multiple criteria.
i.e.
On sheet 1 a team member submits the form I created detailing;
Location A, completed a Fire Drill for Shift 1 on 1/8/2024, (all information asked on form),
Then
I need on sheet 2, the cell related to Location A, to autofill the date (1/8/2024) the drill was completed. i.e. (if location A, completes, fire drill, for shift 1, then display date completed).
I'm thinking it's a version of IF function, however could not locate anything.
Here's what I have so far:
=IF(COUNTIFS({Emergency Procedure Review Data Sheet Range 4}, @cell = "Edenton Ridge Apartments", {Emergency Procedure Review Data Sheet Range 2}, @cell = "Fire Drill Procedures", {Emergency Procedure Review Data Sheet Range 1}, AND(HAS(@cell, "1st"))), DATEONLY({Emergency Procedure Review Data Sheet Range 9}), "not completed")
or would =Index work better?
=INDEX(COLLECT({Emergency Procedure Review Data Sheet Range 9}:{Emergency Procedure Review Data Sheet Range 9},{Emergency Procedure Review Data Sheet Range 4}:{Emergency Procedure Review Data Sheet Range 4}, Edenton Ridge Apartments @row,{Emergency Procedure Review Data Sheet Range 4}),1)
Both returning Unparseable
Thank you,
Answers
-
Hi @brent.kendall -- can you provide a screenshot of the target sheet columns? That will make it easier to understand what might be going wrong.
-
I need the date "2024-01-08" (row 36) from sheet 1 to be displayed on "sheet 2" next to "Edenton Ridge Apartments" (Sheet 2) if the row containing "2024-01-08 contains "Edenton Ridge Apartments", "Fire Drill Procedures", and "1st".
-
This might get you in the right direction. Use this formula on sheet 2 (which should also have references back to sheet 1):
=if(
AND(
index({procedure},XX,0)="fire",
index({shift},XX,0)="1st"
),
index({Created},XX,0),
"")
where XX =
Match("Edenton",{Procedure})
The only issue is that this will pull the first instance of Edenton it finds. It's problematic if you have more than one Edenton. And is there a possibility that you'll have multiple instances of lines having Edenton, fire, and 1st?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives