Using CONTAINS in INDEX/COLLECT cross-sheet formula

Options

Can I use the CONTAINS function within an INDEX/COLLECT cross-sheet formula? I need to pull the Finish dates from a schedule for Tasks that contain a designated text string. There will be multiple tasks that contain that text string. Here is the formula that I have used so far, but I am getting an #INCORRECT ARGUMENT SET error right now. I appreciate any assistance!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot and the formula for context?

  • Steve@VHL
    Options

    @Paul Newcome Thanks for responding to my question. I intended to include the formula in my original post. Here it is: =INDEX(COLLECT({Finish}, CONTAINS("Text String", {Task Name}), {Level}, 1), 1). Here are definitions of some of the components of the formula:

    • Finish: cross-sheet reference to Finish dates on a very long project schedule that contains repeating tasks for seven levels.
    • Task Name: obviously, this is the Task Name column in the project schedule. The Milestones that I am trying to track are not an exact match with the Task Name--they are contained within but not the full Task Name (therefore, the CONTAINS function).
    • Level: designation of what level (1-7) within the project schedule a task is.

    Sending a screenshot won't really work, as this formula is part of a much bigger solution. It is part of a Metrics sheet that I am attempting to provide my client with the earliest Finish date and the latest Finish date across all levels.

    It seems like it is a syntax question that I need to resolve and apply to multiple occurrences. I appreciate your input.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this instead:

    =INDEX(COLLECT({Finish}, {Task Name}, CONTAINS("Text String", @cell), {Level}, 1), 1)

  • Steve@VHL
    Options

    @Paul Newcome Thank you so much! That is exactly what I needed! I need to bone up on the @cell function. That seems to have been the missing piece here.

    By the way, I will be in Seattle for ENGAGE and the Partner Summit. I hope to meet you while I am there. You are my most reliable resource here on the Community. I don't ask questions very often, but when I do, you seem to be the one I can count on to come through. Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!