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

Dawn McCallister
Dawn McCallister ✭✭✭✭✭
in Formulas and Functions

Good Morning - I am working on a formula that pulls the most recent status update (Captured on Sheet A) into the project reporting sheet (Sheet B) based on Project Number on both sheet matching. Restated in formula: Find the most recent record created on Sheet A that matches sheet B Project number and populate cell for status update (sheet b) from data in corresponding status update cell in sheet A)

Requirement - Engineer Resources needs/wants the ability to look back as the historical updates they provided for projects over the life of the project. These project span over a time period of 1 year to 4 years. That is a lot of data to retain in a single cell on the high level project record for executive reporting.

Solution: Created a sheet to capture the weekly status updates. The engineer will complete a smartsheet form weekly with the updated detail. Once the detail is updated that new record should push the new detail to the existing high level record used for reporting and push it to the appropriate record line.

Sheet A - Weekly Project Status Log

image.png

Sheet B - Operations Project Log (project schedule, budget sheets and status update sheet - feeds this record for reporting and single location visibility)

image.png

Current Formula

=INDEX({2022 Operation Project Status Update Log Range 4}, MATCH(MAX(COLLECT({2022 Operation Project Status Update Log Range 2}, {2022 Operation Project Status Update Log Range 3}, [Project #]@row)), {2022 Operation Project Status Update Log Range 2}, 0))

As you can see it is capturing the last update but it is pulling it into multiple records. Any help would be appreciated. I have been fiddling with formula variances for about a month now and I need to nail it for this months report out.


Thank you Dawn

· Share on FacebookShare on Twitter

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Dawn McCallister

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Dawn McCallister
    Dawn McCallister ✭✭✭✭✭
    Answer ✓

    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))

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Dawn McCallister

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Dawn McCallister

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Dawn McCallister
    Dawn McCallister ✭✭✭✭✭
    Answer ✓

    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))

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Dawn McCallister

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Dawn McCallister
    Dawn McCallister ✭✭✭✭✭

    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))

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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.)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Dawn McCallister
    Dawn McCallister ✭✭✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Dawn McCallister
    Dawn McCallister ✭✭✭✭✭

    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 !!

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/321524#Comment_321524

    Happy to help, I'm glad you were able to get it working.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!