Need help detecting the reason for a strange behavior of a UDF
I have used a UDF function in sheet1!C7 and it shows me the expected result. But when I used this function in a cell outside sheet1 (sheet2!A1) with the same input, it unexpectedly showed me zero. (When I first wrote this UDF function in sheet2!A1, it gave me the expected result, but when I clicked on it, surprisingly its value changed and it showed zero. It seems this UDF function has this problem when dealing with inputs which are in another worksheet. The reason I am using this UDF function except Excel function like Vlookup is that my routine work in excel is such that I should constantly determine the value of some values that I do not know their exact position)
In addition to this problem, another problem is that when referring to another workbook, this UDF function gives #VALUE! Error when that workbook is closed.
It would be very kind of you, if you give me some guides on how to deal with such problems.
|