Using COLLECT within multiple IF function

Dora Berky

Hi,

I have an issue with a formula where I'm trying to use COLLECT within IF, but the formula returns "#INVALID COLUMN VALUE" and I can't figure out why.

I have a sheet where I would like to return the Lead Time of each product based on the product name and the mode of transport.

The information of Lead Times I have in another sheet (which I use as reference) where the product name and the different lead times are stated in one row, so the formula needs to pick up which column to use based on the product name.

What I want to achieve is, when someone fills in the product name and the mode of transport in their respective columns, the lead time is returned automatically in the Lead Time column.

So far I have this:

=IF([Mode of Transport]@row = "Air", COLLECT({Reference pricing list Range 1}, {Product Description}, [Product Description]@row), IF([Mode of Transport]@row = "Sea", COLLECT({Reference pricing list Range 2}, {Product Description}, [Product Description]@row), ""))

Maybe if IF and COLLECT is not even the right approach, but it seemed logical for me.

Thanks for any tips in advance!

Genevieve P.

Hi @Dora Berky

An IF( statement that uses COLLECT is a great idea! However the COLLECT function needs to be wrapped within another function, such as MIN or MAX or, what I would suggest, JOIN.

Try this:

=IF([Mode of Transport]@row = "Air", JOIN(COLLECT({Reference pricing list Range 1}, {Product Description}, [Product Description]@row)), IF([Mode of Transport]@row = "Sea", JOIN(COLLECT({Reference pricing list Range 2}, {Product Description}, [Product Description]@row))))

If there are multiple cells that meet the criteria of [Product Description]@row in your Range 1 or Range 2 then you will have multiple values squished together returned. Let me know if that's what you're seeing and I'm happy to help come up with some other solution! In that instance we could maybe use a SUMIF instead.

Cheers,

Genevieve

chen

https://community.smartsheet.com/discussion/comment/267358#Comment_267358

Hi @Genevieve P.

I have a formula used twice COLLECT the same range and pop "#Invalid operation" error. Is COLLECT has some limitation when use? My formula is like this: “=IF(MAX(COLLECT([date1]:[date1], Cell:Cell, "Cell-1")) > 0, MAX(COLLECT([date1]:[date1], Cell:Cell, "Cell-1")), 0)”

+ comment: I`ve found the issue that the result of my MAX() is a date type, it can not compare with number value 0 directly. I replace 0 with another date type value for example DATE(1990,1,1) then it`s ok now.

Genevieve P.

Hi @chen

Thanks for following-up with what you found out! 🙂