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,

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.