Best Of

Re: Nested Index Collect to gather data from multiple sheets

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

Kelly Moore

1 ·

Re: VLOOKUP and COUNTIFS

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].

Dakota Haeffner

1 ·

Re: VLOOKUP and COUNTIFS

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!

bisaacs

2 ·

Help Article updates: Bridge, DataMesh, and Calendar App Learning Tracks

Hello Community,

We’ve created three new collections of articles that highlight specific capabilities:

To view the entire catalog of help articles, visit the Help and Learning Center.

Were the articles helpful?

Let us know by providing feedback at the end of each article.

FVPMxYlVnmjSczPQNBQll8PquodIz0uCYxcHVc23PxG4gfROZ7ySArxXbuk5As2pqbSkb-Av9K2sjnCFG-ZtJVCWYJXHaZXofHITS9ivi6ssviATC3CWw2E176nwBeuKUKB2_bIX2rX6hT_-Z4QKj58

Trelawney

3 ·

Re: Do Data Shuttle Input Expressions work to build a key column?

Thanks for the confirmation, that's what I did after. Just wanted to know if that was possible without creating the column in the source file.

lschek1

1 ·

Re: Formula - Count IFS

Hey Michelle,

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

Dakota Haeffner

1 ·

Re: Whats the formula to find todays date between a range of dates to provide a value?

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!

bisaacs

1 ·

Re: Can I use multiple contains in a formula?

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