#INVALID COLUMN VALUE with COLLECT Function

I am attempting to create formulas to return the next milestone date and next milestone description from a project schedule. Here is what I have currently:

As you can see, the next milestone cell is working as intended. Here is the formula I used to achieve that: =MIN(COLLECT(Finish:Finish, Finish:Finish, @cell >= TODAY(), Milestone:Milestone, @cell = 1))

I am now trying to get a formula to return the description of the next milestone, but I am getting an #INVALID COLUMN VALUE error. Here is the formula I am using there: =COLLECT([Task Name]:[Task Name], Finish:Finish, [Next Milestone]1, Milestone:Milestone, 1)

I was able to get the desired result for this group of dates by using this formula: =INDEX([Task Name]:[Task Name], MATCH([Next Milestone]1, Finish:Finish)). The problem with it is if there happens to be a non-milestone date that matches the next milestone date, it would return whichever one is first of those dates.

I'm a little over my head at this point, but every time I arrive at a formula that seems like it should work, I get the #INVALID COLUMN VALUE error. I can't understand why because "Task Name" and "Next Milestone Description" are both Text/Number type columns.

I would appreciate any insight any of you might have.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!