# If or Collect statement to bring back correct pricing

✭✭✭✭

A vendor has given us a step price increase over the next 3 years. But I can't figure out the best formula to bring back the correct pricing.

I have 13 sheets that will reference this sheet for pricing. In order for the pricing to generate for our use, I need it to be correct for the year.

It's going to be complicated. Index/Match would be based on the entry Date.

For the original price increase I was able to state =IF(Date@row < [Henkel Pricing update]@row, 0.98, 1.01). But this became more complicated.

I think it will be a collect function and I'll keep trying that. Appreciate the help.

Tags:

• ✭✭✭✭✭✭

I would suggest having each price on its own row instead of spread out across multiple columns. Then you would use a MAX/COLLECT in the target sheet to pull in the appropriate start date.

=MAX(COLLECT({Reference Sheet Start Date Column}, {Reference Sheet Start Date Column}, @cell <= [Date Column Name]@row))

Then to pull in the pricing you would use an INDEX/COLLECT like so:

=INDEX(COLLECT({Reference Sheet Price Column}, {Reference Sheet Start Date Column}, @cell = [Date Helper Column]@row, {Reference Sheet Chem Column}, @cell = [Chem Column]@row), 1)

• ✭✭✭✭

I restructured the pricing table.. let me try again.

Amazing! Just changed the @cell" to lower case and it worked. Thank you so much!!!

• ✭✭✭✭✭✭

I would suggest having each price on its own row instead of spread out across multiple columns. Then you would use a MAX/COLLECT in the target sheet to pull in the appropriate start date.

=MAX(COLLECT({Reference Sheet Start Date Column}, {Reference Sheet Start Date Column}, @cell <= [Date Column Name]@row))

Then to pull in the pricing you would use an INDEX/COLLECT like so:

=INDEX(COLLECT({Reference Sheet Price Column}, {Reference Sheet Start Date Column}, @cell = [Date Helper Column]@row, {Reference Sheet Chem Column}, @cell = [Chem Column]@row), 1)

• ✭✭✭✭
edited 05/16/24

Paul,

I am missing something here.. I get an "invalid column name" result.

=MAX(COLLECT({PriceStartDate}, {PriceStartDate}, @cell <= Date@row))

• ✭✭✭✭✭✭

"Invalid Column Name" or "Invalid Column Value"?

If you used the wrong column name in a formula, it will throw the "Unpareseable" error.

The only error message that is output that starts with "Invalid Column" is "Invalid Column Value". If that's the error you are getting, I would start by checking to make sure the column type is correct. Assuming you are pulling a date type value, you need the formula to be in a date type column.

• ✭✭✭✭

Dang Paul.. I have done that before. Thanks!!!!

• ✭✭✭✭

Next is pulling the pricing in. There are a LOT of columns across for different chemicals. So i don't have a @cell {chem name} unless i add in a bunch of columns to point to.

• ✭✭✭✭

=INDEX(COLLECT({PRICING}, {PriceStartDate}, @CELL=[Pricing test date]@row, {CHEMCOLUMN}, @CELL=[8686type]@row), 1)

I am trying! :)

• ✭✭✭✭✭✭

That is why I mentioned in my first comment about restructuring your reference table. Also keep in mind that @cell should be all lower case.

• ✭✭✭✭

I restructured the pricing table.. let me try again.

Amazing! Just changed the @cell" to lower case and it worked. Thank you so much!!!

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!