Return a date from another sheet with multiple criteria

Rachel Fogleman

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!

hollyconradsmith

Hi @Rachel Fogleman ,

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

Rachel Fogleman

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")

hollyconradsmith

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.

Rachel Fogleman

@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"))

hollyconradsmith

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.

Rachel Fogleman

That worked! Thank you so much! :)