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: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.