Formula to pull data from Sheet A to Sheet B based on oldest entry and matching criteria

The first thing I would do is update your range names to include the column, so that you can tell by looking at the range name what column you are referencing in the remote sheet. It's nearly impossible to troubleshoot this because {2022 Operation Project Status Update Log Range 4} means nothing to me, whereas {2022 Operation Project Status Update Accomplished} might; same with the other ranges, I can't tell what data they are referring to.

Totally makes sense Jeff.

Here is the updated formula, hopefully this helps

=INDEX({2022 OPS Status Log - What Was Accomplished}, MATCH(MAX(COLLECT({OPS Status Log - Date Update Submitted}, {OPS Status Log - Project #}, [Project #]@row)), {OPS Status Log - Date Update Submitted}, 0))

Much better! I always recommend that as a best practice, it helps so much, especially when you need to go back months or years later to figure out how you made something work!

I think you need to add a helper column to the Weekly Project Status Log. In this "UniqueValue" column, you essentially want to create a unique value from the Project Number and the Date Update Submitted values. I use something simple like concatenating them together with a hyphen:

=[Project #]@row +"-"+[Date Update Submitted]@row

So now you have, for example, "OPS0330-2/23/22" in your UniqueValue column.

Now, on your Operations Project Log sheet, add a "MaxDate" helper column to collect the max date for that row's project # from the Weekly Project Status Log.

=MAX(COLLECT({OPS Status Log - Date Update Submitted}, {OPS Status Log - Project #}, [Project #]@row)

Now you use the unique value you created in the Weekly Project Status Log sheet as the MATCH basis to pull the What Was Accomplished value:

=INDEX({2022 OPS Status Log - What Was Accomplished}, MATCH(([Project #]@row +"-"+MaxDate@row), {{2022 OPS Status Log - Unique Value}, 0))

English: Find the what was accomplished value in 2022 Ops Status log where [Project #]-MaxDate matches the 2022 Ops Status Log UniqueValue.

All comments

Totally makes sense Jeff.

Here is the updated formula, hopefully this helps

Much better! I always recommend that as a best practice, it helps so much, especially when you need to go back months or years later to figure out how you made something work!

=[Project #]@row +"-"+[Date Update Submitted]@row

So now you have, for example, "OPS0330-2/23/22" in your UniqueValue column.

Now, on your Operations Project Log sheet, add a "MaxDate" helper column to collect the max date for that row's project # from the Weekly Project Status Log.

=MAX(COLLECT({OPS Status Log - Date Update Submitted}, {OPS Status Log - Project #}, [Project #]@row)

Now you use the unique value you created in the Weekly Project Status Log sheet as the MATCH basis to pull the What Was Accomplished value:

=INDEX({2022 OPS Status Log - What Was Accomplished}, MATCH(([Project #]@row +"-"+MaxDate@row), {{2022 OPS Status Log - Unique Value}, 0))

English: Find the what was accomplished value in 2022 Ops Status log where [Project #]-MaxDate matches the 2022 Ops Status Log UniqueValue.

Jeff

I am receiving an "invalid column value" error for the MaxDate Helper column on my Operations Project Log sheet.

Here is the formula copied from my sheet

=MAX(COLLECT({OPS Status Log - Date Update Submitted}, {OPS Status Log - Project #}, [Project #]@row))

https://community.smartsheet.com/discussion/comment/321459#Comment_321459

Make sure your MaxDate column is a date-type column.

Formula Error Messages | Smartsheet Learning Center

#INVALID COLUMN VALUE

Cause

The formula contains or references a data type that is inconsistent with the column type where it is inserted. For example, this MAX formula is placed in a Text/Number column and references other values in the Date column...

=MAX([Due Date]1, [Due Date]52)

...If the column that contains the formula is not a Date column type, the latest date in the range cannot be returned. (See the MAX Function Help article for more information on how the MAX function works.)

Resolution

Make one of the following adjustments to your sheet:

Type the formula in a different column, one where its type aligns with the type of value that the formula is expected to return.
Right-click the column header at the top of the column and select Edit Column Properties. Change the column to a type that aligns with the type of value that the formula is expected to return.
Add IFERROR to your function such that it returns a different value if it encounters the #INVALID COLUMN VALUE error, for example: =IFERROR(“Not a number!”, MAX([Due Date]1, [Due Date]5))
Wrap your formula in a function that can convert it to the expected data type. For example: =WEEKNUMBER(MAX([Due Date]1, [Due Date]5) when placed in a Text/Number column returns a number that corresponds with the highest date’s week number out of a 52 week period. (More on WEEKNUMBER here.)

Oh My - Is it Monday ... Thank you Jeff .. I knew this not sure where my head was. Finishing the build and then I will let you know how it goes.

Jeff - I am back in business !! :) Thank you so much for the help. I was trying to accomplish it in one formula and the help column recommendation did the trick and made it a lot easier. thank you !!