Range data type doesn't work with ranges from other sheets?

This function returns a random value from a range, but only if it meets the criteria in another column.

Item color? animal?
red yes no
cow no yes
dog no yes
blue yes no
ferrari no no

RetrunIf(A2:A6,"yes",2) = red or blue
RetrunIf(A2:A6,"yes",3) = cow or dog
RetrunIf(A2:A6,"yes",2,3) = red or blue or cow or dog

It works great... unless I reference a range on another sheet.
RetrunIf(otherSheet!A2:A6,"yes",2) = "blank"

Is there a problem with using the Range data type for ranges on other sheets?
Here she is: (Disclosure: I am not the author. Was purpose built for me by a coworker, and I only have a basic understanding of how it works.)

Please Login or Register  to view this content.

Originally Posted by TMShucks

I would try changing the three occurrences of Cells to Source.Cells


Regards, TMS

Thanks TM, I tried that.
But it seems I didn't assess the situation completely accurately.
It seems to be a problem with the source sheet. I can't get the function to work natively from the same sheet either. (same workbook, same modules).
I know that if the function can't find any matching examples, it will return a blank. So it would seem that it is unable to find my matching criteria, even though it should be. Maybe there's a hidden character or something.
I'll keep testing and see if I can find a clearer example of what exactly isn't working...

Ah, no. It seems my initial description was correct.
Changing 'Cells' to 'Source.Cells' causes the function to return blank all the time. Thanks for the effort though

Works: called from testResources!AC20 =ReturnIf(testResources!$G$20:$G$20000,"yes",COLUMN(testResources!$I:$I),COLUMN(testResources!$J:$J))
Doesn't work: called from testTrader!D4 =ReturnIf(testResources!$G$20:$G$20000,"yes",COLUMN(testResources!$I:$I),COLUMN(testResources!$J:$J))

Originally Posted by Norie

Try changing Cells to Source.Parent.Cells.

Source.Parent will return the worksheet Source is on.

Ninja'd.
Thanks, I'll try it.

Originally Posted by Norie

Try changing Cells to Source.Parent.Cells.

Source.Parent will return the worksheet Source is on.

Yes, that seems to do the trick. It now works from anywhere. Kudos :D and thanks everybody for your time!