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.


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

  • Elizabeth Aird
    Elizabeth Aird ✭✭✭✭
    edited 05/17/24 Answer ✓

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

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!