Pull Date from Reference sheet by using collect option

Options

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.

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/12/21 Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/12/21 Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • prathap.krishnashetty69881
    Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!