Best Of

Paul, thank you so much for this solution!!! Just what I needed for my application, simple, elegant and not something I would come up with!

Hello @Enoch Mak

I'd recommend having the table of list of project statuses and duration thresholds on a different sheet.

Then try this formula on your Project Status Health column:

=IF([Duration (Subtract from today)]@row > INDEX(COLLECT({Duration Threshold}, {List of Project Statuses}, [Project Status]@row), 1), 1, 0)

Oh wow. What a puzzle! Here's what I've come up with. It is very much an incomplete solution, though, so I'll walk through the logic - I did not solve for EVERYTHING, because it starts to get more and more recursive. You'll know what your dataset can tolerat though.

=IFERROR(MID([Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1), FIND(" ", [Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1) + 1) - FIND("TDL-", [Deliverable Name]@row, 1)), "Investigate")

MID - pulls the number of characters out of [Deliverable Name]@row
FIND #1 - looks for position of "TDL-" starting at the beginning of the cell, and returns a number.
FIND #2 & #3 - looks for the first space AFTER the number in #1 above.
FIND #4 - position of space minus position of "TDL-" = the length to return
IFERROR - if you don't find TDL- …including the dash or something breaks, it returns a flag for further investigation.

You can apply the same logic for finding the TDL ### items, but based on the dataset size, you might want to do a manual review with this flag. I also EMPHATICALLY recommend that you put constraints in place to require people to input the TDL number with constraints using a form, and then separately the deliverable name, and then you rebuild the two together in the background.

Good luck!

I was able to do this but had to add an additional column to the sheet. I have the contact column, text contact column, then a third to extract the email address. I populate the contact in the first column, do a simple copy/paste into the second (text), and have a column formula for the 3rd that extracts the email only using this:

=SUBSTITUTE(MID([Text Contact]@row, FIND("<", [Text Contact]@row) + 1, LEN([Text Contact]@row) - (FIND("<", [Text Contact]@row) + 1)), ">", "")

@Peggy Parchert

This worked! Thank you so much for all of your help, I really appreciate it!!

Hi @santiago.rendon

I hope you're doing well!

To set this up, follow these steps:

1. Add a column to both sheets and name it "Review Complete". Make it a checkbox-type column.

2. In the Request sheet, add the following formula in the Review Complete column:

=IFERROR(INDEX({Review Sheet Review Completed}, MATCH([Task Number]@row, {Review Sheet - Task Number}, 0)), "Not in sheet")

This will pull the "Review Completed" status from the Review Sheet based on the matching Task Number.

Let me know if you have any questions.

Thanks, Peggy

Hi @santiago.rendon

I'm glad you figured it out! It's good practice to name a range instead of using the default one. I'd recommend updating Review Sheet Range 1 and Review Sheet Range 2 with a meaningful name.

Have a great week!

Peggy

Hi @santiago.rendon

Yes, but you'll need to switch to INDEX/COLLECT for this to work. To demonstrate, I created Review Sheet 2, which is identical to Review Sheet 1.

In the Request Sheet - Review Complete column, I added the following column formula:

=IFERROR(IFERROR(IF(Task@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task}, Task@row, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task}, Task@row, {Review Sheet 2 Task Number}, [Task Number]@row), 1)), "not in sheets")

Breakdown of the Ranges:

• {Review Sheet 1 Review Completed} → "Review Completed" column in Review Sheet 1
• {Review Sheet 1 Task} → "Task" column in Review Sheet 1
• {Review Sheet 1 Task Number} → "Task Number" column in Review Sheet 1
• {Review Sheet 2 Review Completed} → "Review Completed" column in Review Sheet 2
• {Review Sheet 2 Task} → "Task" column in Review Sheet 2
• {Review Sheet 2 Task Number} → "Task Number" column in Review Sheet 2

This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".

Let me know if you need any adjustments!

Peggy