1. Don't name a module the same as a function - it'll confuse Excel.
2. You need to use sheet-qualified references. It will work on other sheets interrogating any (other) sheet within a workbook. The udf won't work on closed workbooks.
3 Pecoflyer's solution here:
https://www.msofficeforums.com/169449-post4.html will work on closed workbooks. Be aware that this will give accurate results as long as:
Quote:
Originally Posted by soroush.kalantari
The values which I want to determine their rows are unique and occur just one time
|
If you're using Office365 the formula can be written into a LET formula which means the range you're searching need only be referred to once in the formula, or easier, it could be written into a LAMBDA function which you'd use in just the same way as you're using the UDF I wrote for you earlier.