Show Date of Form Submission if criteria are Met

brent.kendall

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,

Lucas Rayala

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.

brent.kendall

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".

johngraham78

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?