Updating Formula

Options

Hello,

This formula was created for us:

=IF(CONTAINS("Wilson", [Course Type]@row), "", IFERROR(IFERROR(INDEX(COLLECT({Session Start Date}, {Session Title}, "VCoP#1", {Cohort Name}, [Absorb Code]@row), 1), INDEX(COLLECT({Session Start Date}, {Session Title}, "IMP Meeting #1 (asynchronous)", {Cohort Name}, [Absorb Code]@row), 1)), " "))

This is a column formula in our "Intake Sheet". It brings in dates from a separate spreadsheet that are connected to the words in quotes. These words and dates are coming in from Salesforce. Then the "Intake Sheet" formula sends the information out into our tasks sheets for individual projects.

**I want to add "In-Person CoP#1" as one of the words pulling in the date data. Whenever I follow the same coding, the formula comes back as "Incorrect Argument Set". This is what I've typed in and it comes back as an error:

=IF(CONTAINS("Wilson", [Course Type]@row), "", IFERROR(IFERROR(INDEX(COLLECT({Session Start Date}, {Session Title}, "VCoP#1", {Cohort Name}, [Absorb Code]@row), 1), INDEX(COLLECT({Session Start Date}, {Session Title}, "In-Person CoP#1", {Cohort Name}, [Absorb Code]@row), 1), INDEX(COLLECT({Session Start Date}, {Session Title}, "IMP Meeting #1 (asynchronous)", {Cohort Name}, [Absorb Code]@row), 1)), " "))


What am I doing wrong? How do I add this?

Thanks for your help!

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    try the below:

    Just some parentheses changes

    =IF(CONTAINS("Wilson", [Course Type]@row), "", IFERROR(IFERROR(INDEX(COLLECT({Session Start Date}, {Session Title}, "VCoP#1", {Cohort Name}, [Absorb Code]@row), 1), INDEX(COLLECT({Session Start Date}, {Session Title}, "In-Person CoP#1", {Cohort Name}, [Absorb Code]@row), 1)), INDEX(COLLECT({Session Start Date}, {Session Title}, "IMP Meeting #1 (asynchronous)", {Cohort Name}, [Absorb Code]@row), 1),""))

  • rphelps
    rphelps ✭✭
    Options

    Thank you Leibel, but no that did not work. Still came back as "Incorrect Argument Set"

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    sorry, try this:


    =IF(CONTAINS("Wilson", [Course Type]@row), "", IFERROR(IFERROR(INDEX(COLLECT({Session Start Date}, {Session Title}, "VCoP#1", {Cohort Name}, [Absorb Code]@row), 1), INDEX(COLLECT({Session Start Date}, {Session Title}, "In-Person CoP#1", {Cohort Name}, [Absorb Code]@row), 1)), INDEX(COLLECT({Session Start Date}, {Session Title}, "IMP Meeting #1 (asynchronous)", {Cohort Name}, [Absorb Code]@row), 1)))

  • rphelps
    rphelps ✭✭
    Options

    Yes! That worked, thank you! But now a new issue has come up. We have 10 total columns gathering this information from Salesforce- VCoP#2, VCoP#3, etc. I planned to add "In-Person CoP#2", etc. to these formulas. However, the cell is accepting the "VCoP#1" date instead of the "In-Person CoP#1" date because they are both available options, but the In-Person date comes before the VCoP date- wasn't considering that part. If I move the "In-Person CoP#1" as the first option in the formula- will it gather that information first? Or do we need to come up with a new solution?


    thank you again!

  • rphelps
    rphelps ✭✭
    Options

    Hi Leibel, can you let me know what was so different between the 2 codes that you sent to make it work the second time?


    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!