Generate Value on Sheet Based on List Values in Other Sheet

refry

Hi

I'm trying to find the best way for another sheet to use Project Number to check this sheet and see if the Project Number is listed here with an embargo status of "ACTIVE". If true, then I would like for it to return a value of "ACTIVE" in the field. If not true, then I'd like it to return a value of "NO EMBARGO".

If the above is true,

In the next column I'd like for it to pull in the earliest Embargo Release Date where Embargo Status is "ACTIVE" for that Project Number. In the next column, I'd like for it to return the Embargo details associated with the min Embargo Release Date where status is "ACTIVE".

I'm not sure the best way to do this…I was thinking a combination of IF INDEX(MATCH, but I'm at a loss.

Appreciate any guidance.

Georgie

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