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

Todd Lozo

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), ""))

Thanks!

Mike TV

@Todd Lozo

I'm confused by the very start of your formula. It's saying to do something if the entire range of {2021 MCE Turbine S/N} is equal to the value in a single cell. That doesn't sound right or possible to me. Are you sure that is correct?

Todd Lozo

Yeah, that's not right.

In plain English, it should translate to:

"If the serial number @row on the target sheet matches the serial number in any of the rows on the source sheet, then pull the RTS date from the source to the target."

Hopefully that helps clarify. It's very possible that I need to use another formula - I'm still learning.

Hollie Green

You have a criteria range without a criteria specified in your formula. For your collect formula based on how you have it it is trying to find a criteria for the component range but you close it out before telling it what criteria you want to have it look for. If the component should equal 1 you need to move your parenthesis after component} to after your next 1.

Paul Newcome

I would suggest looking into an INDEX/MATCH.

=INDEX({Range To Pull}, MATCH([Serial Number]@row, {Range To Match In}, 0))