Formula Assistnace

Sarah.Cordell

Good morning!

I need help with how to write a formula for the following situation. As an example:

Here is example source data.

I need to pull on another sheet the date of the last 'oil change' in this case for that specific person, and in the next column notate the status of that last notated oil change.

I hope that makes sense, thank you in advance!

KPH

Hi @Sarah.Cordell

Date formula

You can use a COLLECT function to collect all the dates where the name matches the name in your output table, and the activity is oil change. Then use MAX to return the latest date in the collection. If the output table was in the same sheet as the source data, the formula would look like:

=MAX(COLLECT(Date:Date, [Name]:[Name], Name@row, Activity:Activity, "Oil Change"))

To create this table in a separate sheet, you will need to set up cross sheet references for each of the columns. The result would be something like:

=MAX(COLLECT({Source Data -Date}, {Source Data -Name}, Name@row, {Source Data -Activity}, "Oil Change"))

Status formula

You can use another COLLECT function to collect all the statuses where the name matches the name in your output table, and the date matches the date in your output table. Then use INDEX to return the status from the first row that matches. If the output table was in the same sheet as the source data, the formula would look like:

=INDEX(COLLECT(Status:Status, [Name]:[Name], Name@row, Date:Date, [Date of last oil change]@row), 1)

Once you set up cross sheet references for each of the columns, the result would be something like:

=INDEX(COLLECT({Source Data -Status}, {Source Data -Name}, Name@row, {Source Data -Date}, [Date of last oil change]@row), 1)

I would add an IF ISBLANK to this, so if the date is blank (because there was no date matching the date formula as the person has not done an oil change), the status returned is "" (nothing), rather than an error message.

IF ISBLANK looks like this:

=IF(ISBLANK([Date of last oil change]@row), "", original formula)

So the final formula is:

=IF(ISBLANK([Date of last oil change]@row), "", INDEX(COLLECT({Source Data -Status}, {Source Data -Name}, Name@row, {Source Data -Date}, [Date of last oil change]@row), 1))

Result

Your output table (based on the example you shared) will look like this:

KPH

Hi @Sarah.Cordell

Date formula

You can use a COLLECT function to collect all the dates where the name matches the name in your output table, and the activity is oil change. Then use MAX to return the latest date in the collection. If the output table was in the same sheet as the source data, the formula would look like:

=MAX(COLLECT(Date:Date, [Name]:[Name], Name@row, Activity:Activity, "Oil Change"))

To create this table in a separate sheet, you will need to set up cross sheet references for each of the columns. The result would be something like:

=MAX(COLLECT({Source Data -Date}, {Source Data -Name}, Name@row, {Source Data -Activity}, "Oil Change"))

Status formula

You can use another COLLECT function to collect all the statuses where the name matches the name in your output table, and the date matches the date in your output table. Then use INDEX to return the status from the first row that matches. If the output table was in the same sheet as the source data, the formula would look like:

=INDEX(COLLECT(Status:Status, [Name]:[Name], Name@row, Date:Date, [Date of last oil change]@row), 1)

Once you set up cross sheet references for each of the columns, the result would be something like:

=INDEX(COLLECT({Source Data -Status}, {Source Data -Name}, Name@row, {Source Data -Date}, [Date of last oil change]@row), 1)

I would add an IF ISBLANK to this, so if the date is blank (because there was no date matching the date formula as the person has not done an oil change), the status returned is "" (nothing), rather than an error message.

IF ISBLANK looks like this:

=IF(ISBLANK([Date of last oil change]@row), "", original formula)

So the final formula is:

=IF(ISBLANK([Date of last oil change]@row), "", INDEX(COLLECT({Source Data -Status}, {Source Data -Name}, Name@row, {Source Data -Date}, [Date of last oil change]@row), 1))

Result

Your output table (based on the example you shared) will look like this:

Sarah.Cordell

These worked great - thank you so much for your quick response and help!!

KPH

Glad I could help @Sarah.Cordell