How to fill a column with a text string value from another column

Nick_Vastano

I currently have a sheet where I am trying to organize tasks by milestone. I am trying to create two columns for Next Milestone Task Name and Date. I have my Next Milestone Date and part of my Next Milestone Name. I want the column with Next Milestone Name to fill every row in that column unlike how it is now. Here is what I have written so far.

Helper Column

=IF(AND(OR(Status@row = "In Progress", Status@row = "Under Review", Status@row = "Not Started", Status@row = "On Hold"), Milestone@row = 1), [Due Date]@row, "")

Next Milestone Date

=MIN([Helper Column]:[Helper Column])

Next Milestone Name

=IF(AND(Milestone@row = 1, [Due Date]@row = [Next Milestone Date]@row), INDEX([Task Name]@row, MATCH([Next Milestone Date]@row, [Due Date]@row)))

Also, another issue arises when there are two milestones with the same date. How can I prioritize the Task Name that comes first in descending row order?

Genevieve P.

Hi @Nick_Vastano

The reason your "Next Milestone" formula is only filling a few cells is because of your AND statement at the beginning. This is looking in the current row to see if the current row is a Milestone and if it's due date is the same as the Next Milestone Date.

Instead, it sounds like you want toe INDEX part of the formula to look at these two filters.

Try an INDEX(COLLECT instead:

=INDEX(COLLECT([Task Name]:[Task Name], [Due Date]:[Due Date], [Next Milestone Date]@row, Milestone:Milestone, 1), 1)

This puts your filters inside the INDEX formula, by using the COLLECT Function. Does that make sense? You'll also see that at the end of the formula there's a 1. This says to bring back the first row match, so it should bring back the first Task Name in descending order.

Let me know if this works for you!

Cheers,

Genevieve