#INVALID COLUMN VALUE with COLLECT Function

Cory Griffis

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.

Paul Newcome

Try INDEX(COLLECT(.................)) instead of just a COLLECT function. Also check you column type.

Paul Newcome

Try INDEX(COLLECT(.................)) instead of just a COLLECT function. Also check you column type.

Cory Griffis

Thanks, Paul! That worked. Here is the full formula for anyone interested: =INDEX(COLLECT([Task Name]:[Task Name], Finish:Finish, [Next Milestone]1, Milestone:Milestone, 1), 1, 1)

Paul Newcome

Excellent. Happy to help! 👍️

Jennifer Levey

Paul, you mention to check the column type. I'm getting the same error. Can you explain a little more about how certain column types can contribute to this problem (or point me to where this is explained)? Thanks.

Paul Newcome

@Jennifer Levey Certain column types can only accept certain data types. For example, a date type column would need either a date or text value. If you are using a formula that is pulling a date into a text/number column, you may run into the same error as well. There is also the chance that error is present within the range you are evaluating in which case it would pull through your formula even if the formula matches the column type.

If you would like to explain a little more about your setup and provide your formula and expected results, we may be able to shed some light on your particular struggle.

Jennifer Levey

Hi @Paul Newcome. That makes sense. Thank you for that explanation. The Fiscal Year column in the source sheet is a drop-down (with 2019-20, 2018-19, etc.) as values. Essentially I'm trying to pull the Customer Contact Name when the Customer District Name and the Fiscal Year match (the contact can change each year and each customer is in the sheet multiple times).

This was my formula:

=INDEX(COLLECT({Planware Customer_Contact Name}, {Planware Customer_DistrictName}, [Customer Name]5, {Planware Customer_Fiscal Year}, [Fiscal Year]5), 0)

I was able to revise the formula because I thought I was missing a criterion (bolded below) but now I'm getting an Incorrect Argument Error with this formula:

=INDEX(COLLECT({Planware Customer_Contact Name}, {Planware Customer_DistrictName}, [Customer Name]4, {Planware Customer_Fiscal Year}, [Fiscal Year]4, 0), 0)

Was it correct thatt he criterion was missing or is there indeed an issue with using a drop-down field? Thanks for your help, Jennifer

Paul Newcome

The problem is that very last 0 in your first formula. You are telling the INDEX function to pull from row 0 which doesn't exist. Change that to a 1 and see how that works for you...

=INDEX(COLLECT({Planware Customer_Contact Name}, {Planware Customer_DistrictName}, [Customer Name]5, {Planware Customer_Fiscal Year}, [Fiscal Year]5), 1)

Jennifer Levey

Hi Paul, thanks so much, I was reading the wrong formula info!

Paul Newcome

@Jennifer Levey Happy to help! 👍️