Formula to pull data from Sheet A to Sheet B based on oldest entry and matching criteria
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
Sheet B - Operations Project Log (project schedule, budget sheets and status update sheet - feeds this record for reporting and single location visibility)
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
Best Answers
-
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!
-
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.
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!
Answers
-
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!
-
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.
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!
-
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))
-
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!
-
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 !!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!