Best Of

Are you able to provide more details? What exactly are you wanting to accomplish?

Hi @Protonsponge ,much thanks for your reply. I have successfully used this formula for statistics. But I want to know what "HAS" means and how do I use it? Why need to use "@cell"? Thank you!

Hi @Protonsponge . I got it. Very appreciate for your help. This link is so useful! Thank you very much！！😊

Hi @refry,

To pull data from a cell using multiple criteria, you can use INDEX(COLLECT) formulas, and to pull the earliest release date, you can use a MIN(COLLECT) formula.

Here’s what I’d do:

In the sheet in which you’d like to pull the data, create your columns - you can name them as desired, but for this explanation, I’ll refer to them as follows:

• Project Number
• Embargo Status
• Earliest Release Date
• Details

Enter the project number values you wish to pull data for into the Project Number column.

In the following formulas, you’ll need to create cross sheet references as you create the formulas. You’ll need to have these refer to the entire column in your first sheet, so:

• {Status} will be the entire “Embargo Status” column in sheet 1
• {Project Number} will be the entire “Project Number” column in sheet 1
• {Release Date} will be the entire “Embargo Release Date” column in sheet 1
• {Details} will be the entire “Embargo Details” column in sheet 1

Again, you can name the references as desired within the sheet reference manager when you’re creating the references (and you’re able to rename/edit references later), but it’s best to name them so they refer to the column they’re referencing. 

In the Embargo Status column, type the following formula, creating the references as you go:

• =IFERROR(INDEX(COLLECT({Status}, {Project Number}, [Primary Column]@row, {Status}, "ACTIVE"), 1), "NO EMBARGO")

In the Earliest Release Date column, type the following formula, creating any new references as you go (since you’ve already created the {Status} and {Project Number} references, you can simply type these into your formula):

• =MIN(COLLECT({Release Date}, {Project Number}, [Primary Column]@row, {Status}, "ACTIVE"))

In the Details column, type the following formula, creating the {Details} reference and typing in your existing references:

• =INDEX(COLLECT({Details}, {Project Number}, [Primary Column]@row, {Status}, "ACTIVE", {Release Date}, [Earliest release date]@row), 1)

Check out this help article for more information on combining functions for cross-sheet formulas: Formula combinations for cross sheet references.

Does that work for you?

Georgie

Give this a try (update column names to match your sheet):

=INT((TIME(End@row) - TIME(Start@row)) * 24) + ":" + RIGHT("0" + (VALUE(RIGHT(End@row, 2)) - VALUE(RIGHT(Start@row, 2)) + IF(VALUE(RIGHT(Start@row, 2)) > VALUE(RIGHT(End@row, 2)), 60, 0)), 2)

Hello!

Okay first thing is I would put 0 in your MATCH function to search for an exact match instead of 1, which will tell MATCH to look for the largest value less than or equal to your search value.

=IFERROR(INDEX({OCM Membership Directory |Inactive}, MATCH(Email@row, {OCM Membership Directory |Email}, 0)), "TERM-")

Otherwise, check to make sure that cross sheet references are looking at the range you want, ex. the whole column.

Hope this helps and good luck!

Hi @jjg279,

I suspect the issue is due to not using "HAS" for the Food Given portion of the formula. Since that is a multi-select drop down, you should set up the criteria with "HAS" the same way you did for the Name column.

Hope this helps,

Dave