If or Collect statement to bring back correct pricing

Elizabeth Aird

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.

Paul Newcome

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

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

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

Paul Newcome

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

Paul,

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

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

Paul Newcome

"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.

Elizabeth Aird

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

Elizabeth Aird

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.

Elizabeth Aird

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

I am trying! :)

Paul Newcome

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.

Elizabeth Aird

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

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

Paul Newcome

Happy to help. 👍️