Return a date from another sheet with multiple criteria

Options

I have a form where I am collecting data from transit systems by month. I have formulas to other sheets to sum up expenditures and trip data. However, I want one sheet where I can see if a transit system has submitted data for the month.

I'm trying to use a formula to return a date to the "master sheet" and am getting an error message on this index formula:

=IFERROR(INDEX({ROAP Expenditures (approved) Range 3}, MATCH([Transit System]@row, {ROAP Expenditures (approved) Range 2}, AND(MATCH("Nov 2021", {ROAP Expenditures (approved) Range 4}, 0), "No submission"))))

I would like "No submission" to come up if the formula cannot find a submission. I'm getting an #INCORRECT ARGUMENT SET error message. Help!

Rachel Fogleman

rachel.fogleman@mottmac.com

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Options

    Hi @Rachel Fogleman ,

    Try using Index(Collect to designate multiple criteria. https://help.smartsheet.com/function/collect

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Rachel Fogleman
    Options

    Thanks @hollyconradsmith ! I used the JOIN/COLLECT function and I get the date, but the cell comes back blank when it can't find a date instead of putting "No Submission" in the cell.

    =IFERROR(JOIN(COLLECT({ROAP Expenditures (approved) Range 3}, {ROAP Expenditures (approved) Range 2}, [Transit System]@row, {ROAP Expenditures (approved) Range 4}, "Nov 2021")), "No submission")

    Rachel Fogleman

    rachel.fogleman@mottmac.com

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Options

    If you remove iferror, does it return an error message where you are currently seeing blanks? If there is no error message, the iferror function will not return your "No Submission" request.

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Rachel Fogleman
    Options

    @hollyconradsmith That makes sense. There's not an error, but how do I get it to return "No Submission" (or other value) if it doesn't find a date to collect?

    My current formula:

    =JOIN(COLLECT({ROAP Expenditures (approved) Range 3}, {ROAP Expenditures (approved) Range 2}, [Transit System]@row, {ROAP Expenditures (approved) Range 4}, "Jul 2021"))

    Rachel Fogleman

    rachel.fogleman@mottmac.com

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Options

    The only thing I can think of is a big crunchy if statement like this:

    =if(JOIN(COLLECT({ROAP Expenditures (approved) Range 3}, {ROAP Expenditures (approved) Range 2}, [Transit System]@row, {ROAP Expenditures (approved) Range 4}, "Jul 2021"))>0, JOIN(COLLECT({ROAP Expenditures (approved) Range 3}, {ROAP Expenditures (approved) Range 2}, [Transit System]@row, {ROAP Expenditures (approved) Range 4}, "Jul 2021")), "No Submission")

    I don't have the data to really test that, though. Let me know if that ends up working out.

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Rachel Fogleman
    Options

    That worked! Thank you so much! :)

    Rachel Fogleman

    rachel.fogleman@mottmac.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!