Show Date of Form Submission if criteria are Met

brent.kendall
brent.kendall ✭✭
edited 01/10/24 in Smartsheet Basics

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

  • Lucas Rayala
    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.

  • 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?