Can I use an IF statement with a cross sheet reference

Derek V

Hi

I'm trying to use this formula and it returns an Invalid Operation. What am I doing wrong?

=IF({Cross Sheet Range 1} = "", "", IF({Cross Sheet Range 2} = 1, "Yes", "No"))

It works when I reference ranges in the same sheet

Derek V

I figured it out!!!

It's an IF statement combined with an Index Match

kowal

hi @Derek V

of course you can.

Paul Newcome

What exactly is it that you are wanting to accomplish?

Derek V

Hi Paul.

I want to put a green tick in a summary sheet when a certain field in another sheet is filled in. So my logic is if a cell with the project name in the cross sheet reference is blank, then do nothing. If a cell is ticked, then put "Yes" in the summary sheet cell and if it isn't ticked, then put "No".

This formula works when the references are all in the same sheet, but when I substitute the internal references for cross sheet references I get an Invalid Operation error.

=IF([Project Name]@row = "", "", IF([Column9]@row = 1, "Yes", "No"))

Derek V

I figured it out!!!

It's an IF statement combined with an Index Match

Paul Newcome

@Derek V That's exactly what I would have suggested. Glad you were able to get it sorted!