Best Of

@Gillian C

Thank you very much! The formula worked when I changed the 0 to a 1.

Follow up questions, some of the projects are not in my reference sheet, so I am getting an #invalidvalue. Is there a way to either return a 0 or remove these? I applied the formula to my entire column.

@Gillian C Thank you so much! You are a life saver!

Hey @Jess D

Try this

=IFERROR(IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row),1), INDEX(COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row),1)),"Missing Person")

Will this work for you?
Kelly

Got it, I would try something like:

=COUNTIFS([Issue Location]:[Issue Location], "Cath/IR & CP Restock Staff (b)", [Month Reported]:[Month Reported], Month@row)

If this grid is on a separate sheet the ranges will need to reference the sheet column in place of the ranges I put in [brackets].

Hey @shelbylund,

I would write it like so:

=COUNTIFS([Month Reported:[Month Reported], "May", [Issue Location]:[Issue Location], CONTAINS("Cath/IR", @cell)

You'll want to change the cell ranges to sheet references from the source sheet (and change the month/issue location to match the cell the formula is in), but this structure should work!

Hey Michelle,

Could we get some screenshots of the fields that are referenced in the formula?

Hey @Majduleen,

INDEX/COLLECT should provide what you're trying to do:

=INDEX(COLLECT([Primary Column]:[Primary Column], [Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY()), 1)

Hope this helps!