Getting "Invalid Operation" Error when trying to reference 2 sheets

Hi all,

My first time posting in this forum!

I'm trying to pull dates from 2 reference sheets (2021 and 2022 MCE sheets) into a target sheet using the IF function, as well as INDEX(COLLECT functions. The syntax seems right, but I'm getting an "Invalid Operation" error. I've confirmed that column properties for both the source and target sheets are restricted to dates (the value I'm trying to pull).

Basically, if the serial number in my target sheet matches the serial numbers in both the 2021 and 2022 MCE sheets, pull the dates into the target sheet's date column.

Here is the formula:

=IF({2021 MCE Turbine S/N} = [Serial Number]@row, INDEX(COLLECT({2021 MCE RTS Date}, {2021 MCE Turbine S/N}, [Serial Number]@row, {2021 MCE Component}), 1, 1), IF({NAM 2022 MCE S/N} = [Serial Number]@row, INDEX(COLLECT({NAM 2022 MCE RTS}, {NAM 2022 MCE S/N}, [Serial Number]@row, {NAM 2022 MCE Component}), 1, 1), ""))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!