Complex Formula to return latest date a specific value was used and return a value from a dif column

Devon Eddy

Alright, I've got a pretty complex formula (at least for me..) that I've been banging my head against and could definitely use a little help from the awesome community!

Here's what I am trying to do (see screenshots for reference).

I'm attempting to create a formula in the "Latest Backlink Live" Column. I would like the formula to look at the "root domain@row" in screenshot 1 and then use that value, to search "Backlink" column in Screenshot 2. to return the array of rows that contain that root domain from screenshot 1.

The next step with this array is to segment the exact row I'm looking for. I'd only like it to count the row if column status = live & then bring back the one that has the latest date.

Thank you for any help solving this one! -Devon

Mark Cronk

Hi @Devon Anderson

You had an extra paren. Try this:

To get the date: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [root domain]@row, {4.01 Backlinking Range 4}, "Live"))

It looks like your back link doesn't exactly match the root domain. If so, you should add a CONTAINS function: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, CONTAINS([root domain]@row), {4.01 Backlinking Range 4}, "Live"))

Mark

Mark Cronk

Hi @Devon Eddy ,

This formula might help you move in the right direction. It should return the newest date of the live backlink equal to the root domain. Since you are working with 2 sheets the cell ranges will need to be external sheet references; you can't copy and paste it:

=MAX(COLLECT({[date complete]:[date complete]}, {[backlink]:[backlink]}), [root domain]@row), {Status:status}, "Live")

What did you want to return besides the date?

Hope this is some help.

Mark

Devon Eddy

Hey @Mark Cronk thanks for the response!

I tried inputting your formula and replacing everything with cross sheet formulas and am not able to get it to function properly.

Here's how I have it setup: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}), [root domain]@row), {4.01 Backlinking Range 4}, "Live")

It is returning Unparsable

Besides the date, I'm trying to return the value of the latest backlink @row that contains the root domain from the sheet in screenshot 1.

Devon Eddy

Alright @Mark Cronk I got it to work with following formula! =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [Root Domain]@row, {4.01 Backlinking Range 2}, "Live"))

Thanks again for your help.

Now, the other piece I'm trying to get is the actual "backlink" value that is correlating with the max date we just accomplished in the other column. (see screenshot)

I feel like I am very close but cannot get it to work. Here's the formula I have worked out so far: =COLLECT({4.01 Backlinking Range 6}, {4.01 Backlinking Range 1}, [Date Last Backlink Live]@row, {4.01 Backlinking Range 3}, [Date Last Backlink Live]@row)

Range 6 is the Backlink Column (the value I want to actually bring in)

Range 1 is the root domain (same on both sheets)

Range 3 is the date column which should be the same as [Date Last Backlink Live]@row since we just came up with that in the previous formula.

Appreciate any further help very much!

Mark Cronk

Hi @Devon Anderson

You had an extra paren. Try this:

To get the date: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [root domain]@row, {4.01 Backlinking Range 4}, "Live"))

It looks like your back link doesn't exactly match the root domain. If so, you should add a CONTAINS function: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, CONTAINS([root domain]@row), {4.01 Backlinking Range 4}, "Live"))

Mark

Mark Cronk

Hi @Devon Anderson ,

Glad you solved the 1st part while I was typing.

For the Backlink value try:

=JOIN(COLLECT({4.01 Backlinking Range 6}, {4.01 Backlinking Range 1}, [Date Last Backlink Live]@row, {4.01 Backlinking Range 3}, [Date Last Backlink Live]@row)

Work?

Mark