Best Of

@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!

WHy exactly are you using the CONTAINS function? If you are only going to have one or the other you can drop the CONTAINS and just use:

=IF([Named Status]1 = "In Progress", "Blue", IF([Named Status]1 = "Pending 3rd Party", "Yellow", IF([Named Status]1 = "Completed", "Green")))

If you must use the CONTAINS function it would look more like this:

=IF(CONTAINS("In Progress", [Named Status]1), "Blue", IF(CONTAINS("Pending 3rd Party", [Named Status]1), "Yellow", IF(CONTAINS("Completed", [Named Status]1), "Green")))