When I use the same formula but change the element (Lean Leadership Values and Behaviours)

Colin12Smartsheet

This error occurs #invalid value. Any ideas why this is the case

Paul Newcome

That error from an INDEX/COLLECT is essentially telling you there is no match. Your formula is looking for a row that has both "Skills and Capabilities Development" in the Element column and the absolute most recent date in the Capability column. That absolute most recent date is not limited to just "Skills and Capabilities Development" though. Based on the data in your screenshot, there is no row that matches the criteria of your formula.

The most recent date is 11/6/25, but there are no rows that have the "Skills and Capabilities Development" and 11/6/25 on the same row.

My suggestion would be a date type column there in the sheet containing the formula with a MAX/COLLECT used to pull the max date based on [Column Name]@row. Replace "Column Name" with the name of the column in the formula sheet that contains "Skills and Capabilities Development". Cell references like this make formula scaling much easier because it will always reference the data in the specified column on whichever row the formula is in.

This means you can have the same exact MAX/COLLECT on both rows, and the formula will search dynamically saving you from having to update the formula for every row.

Then you would adjust your INDEX/COLLECT to reference this helper Date column.

=INDEX(COLLECT({Source Sheet Column To Pull Over}, {Source Sheet Element Column}, @cell = [Column name]@row, {Source Sheet Date Column}, @cell = [Date Helper Column]@row), 1)

Paul Newcome

That error from an INDEX/COLLECT is essentially telling you there is no match. Your formula is looking for a row that has both "Skills and Capabilities Development" in the Element column and the absolute most recent date in the Capability column. That absolute most recent date is not limited to just "Skills and Capabilities Development" though. Based on the data in your screenshot, there is no row that matches the criteria of your formula.

The most recent date is 11/6/25, but there are no rows that have the "Skills and Capabilities Development" and 11/6/25 on the same row.

My suggestion would be a date type column there in the sheet containing the formula with a MAX/COLLECT used to pull the max date based on [Column Name]@row. Replace "Column Name" with the name of the column in the formula sheet that contains "Skills and Capabilities Development". Cell references like this make formula scaling much easier because it will always reference the data in the specified column on whichever row the formula is in.

This means you can have the same exact MAX/COLLECT on both rows, and the formula will search dynamically saving you from having to update the formula for every row.

Then you would adjust your INDEX/COLLECT to reference this helper Date column.

=INDEX(COLLECT({Source Sheet Column To Pull Over}, {Source Sheet Element Column}, @cell = [Column name]@row, {Source Sheet Date Column}, @cell = [Date Helper Column]@row), 1)