IF/CONTAINS Formula Question

Right now I'm trying to experiment with pulling information from one sheet to another based on if the creation day and submitter match, and I'm struggling to get it to work. First I was getting an "INVALID DATA TYPE" error, so I tried starting from scratch but now I'm getting an "INCORRECT ARGUMENT" error, so I guess progress?? Here's what I have so far:

=IF(AND(CONTAINS(DAY(Created@row), DAY({Master Document Request Submittal Date})), CONTAINS([Created By]@row, {Master Document Request Submitted By}), {Master Document Request ID}, ""))

The logic is that if the current sheet Created Day matches the Master Doc row created Day AND the current sheet submitter matches the Master Doc row submitter, it'll pull the ID # from the Master Doc.

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Scott Orsey

Hi @bisaacs , You'll want to explore the INDEX/COLLECT approach (in situations where you only have 1 condition, you could also use INDEX/MATCH). Search in the Community for some great discussions. In your case, INDEX/COLLECT would look like:

= IFERROR(INDEX(COLLECT({Master Document Request ID},{Master Document Request Submittal Date},Created@row,{Master Document Request Submitted By},[Created By]@row),1),"")

A quick explanation:

COLLECT() returns a range of the values that meet the criteria.
INDEX() tells SS which value from the range you want from COLLECT(). Since you will only have, at max, one value that meets the criteria, we are simply returning the first (and only) value.
IFERROR() will set the value of the cell to "" if nothing is returned which would produce an error since you can't take the "first" of "nothing".

A few notes about the approach you were attempting:

IF() cannot accept ranges. It only operates on single values
DAY() returns the numerical value for the day of the month. so the 15th of March and the 15th of April will both return "15". You lose the month and year.

I hope this helps. Good luck and be well!

All comments

Scott Orsey

= IFERROR(INDEX(COLLECT({Master Document Request ID},{Master Document Request Submittal Date},Created@row,{Master Document Request Submitted By},[Created By]@row),1),"")

A quick explanation:

COLLECT() returns a range of the values that meet the criteria.
INDEX() tells SS which value from the range you want from COLLECT(). Since you will only have, at max, one value that meets the criteria, we are simply returning the first (and only) value.
IFERROR() will set the value of the cell to "" if nothing is returned which would produce an error since you can't take the "first" of "nothing".

A few notes about the approach you were attempting:

IF() cannot accept ranges. It only operates on single values
DAY() returns the numerical value for the day of the month. so the 15th of March and the 15th of April will both return "15". You lose the month and year.

I hope this helps. Good luck and be well!

bisaacs

Hey Scott,

Thanks for this explanation!

Initially I was trying to use the default Smartsheet "Created" column for the date reference, however that didn't end up working. I ended up having to create a separate Date column that just pulled the date from the "Created" column and use that as a reference in the formula, and then it worked perfectly!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.