Need Formula Help Looking for most recent activity type but reviewing 6 columns

KBASNET

I need a formula for my metrics sheet to pull the most recent account engagement type from a separate sheet, however I have multiple columns to search for the most recent engagement type.

The form connected to the sheet allows a rep to select one, two, or three outreach updates so I have three Account Names, three Outreach Dates, and three Account Engagement type columns that the formula needs to review.

this is the formula i have been working with but something is off and i keep getting an error code:

=IF(
MAX(
COLLECT({Outreach #1 Date of Outreach}, {Outreach #1 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #2 Date of Outreach}, {Outreach #2 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #3 Date of Outreach}, {Outreach #3 Account Name (City)}, [Account Name (City)]@row)
) = 0,
"",
INDEX(
COLLECT(
{Outreach #1 Account Engagement Type}, {Outreach #1 Date of Outreach},
MAX(
COLLECT({Outreach #1 Date of Outreach}, {Outreach #1 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #2 Date of Outreach}, {Outreach #2 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #3 Date of Outreach}, {Outreach #3 Account Name (City)}, [Account Name (City)]@row)
),
{Outreach #1 Account Name (City)}, [Account Name (City)]@row
) +
COLLECT(
{Outreach #2 Account Engagement Type}, {Outreach #2 Date of Outreach},
MAX(
COLLECT({Outreach #1 Date of Outreach}, {Outreach #1 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #2 Date of Outreach}, {Outreach #2 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #3 Date of Outreach}, {Outreach #3 Account Name (City)}, [Account Name (City)]@row)
),
{Outreach #2 Account Name (City)}, [Account Name (City)]@row
) +
COLLECT(
{Outreach #3 Account Engagement Type}, {Outreach #3 Date of Outreach},
MAX(
COLLECT({Outreach #1 Date of Outreach}, {Outreach #1 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #2 Date of Outreach}, {Outreach #2 Account Name (City)}, [Account Name (City)]@row),
COLLECT({Outreach #3 Date of Outreach}, {Outreach #3 Account Name (City)}, [Account Name (City)]@row)
),
{Outreach #3 Account Name (City)}, [Account Name (City)]@row
),
1
)
)

Paul Newcome

Are you able to provide some screenshots for context? What is the error you are getting?

KBASNET

Yes, i am getting the #INVALID REF error! Thank you!

KBASNET

I have a variety of columns within the same sheet where a rep would submit either one, two, or three account outreach updates within the same form submission so one, two, or three updates within the same row. I need the formula to look for the specific account name in outreach #1, #2 or #3 and then look at the outreach dates to then determine the most recent date for outreach account engagement type and then return that engagement type

Paul Newcome

That particular error means you have not properly set up at least one of the Cross Sheet References. Double check all of those first.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.