Pull Date from Reference sheet by using collect option

Hi, I'm trying to pull date from another sheet (Sheet2) to Sheet1 using Collect option but the value is showing error as #DATE EXPECTED. I'm not sure where i'm missing the value.

Sheet1:

ERROR = #DATE EXPECTED

FORMULA: =COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1")

Sheet2:

Can i get some help here please.

Find more posts tagged with

Accepted answers

Mark Cronk

Try: =JOIN(COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1"))

COLLECT needs to be used with another function. Use JOIN to bring in text.

Your formula will only work if there's 1 date found. If it returns 2 dates you'll get an error.

Work?

Mark

All comments

Mark Cronk

Try: =JOIN(COLLECT({Date Range}, {Name Range}, [Trainee Name]@row, {Training Name Range}, "Training1"))

COLLECT needs to be used with another function. Use JOIN to bring in text.

Your formula will only work if there's 1 date found. If it returns 2 dates you'll get an error.

Work?

Mark

prathap.krishnashetty69881

Perfect, its working. Thanks so much for the quick help. Much appreciated🙏

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark

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.