When I call the function from Excel, I don't get an error. Adding data to the
various columns, it returns the values we would expect. Are you sure you
copied it into a VBA module (Insert Module in the Visual Basic Editor) in
the same workbook? It must have the Public keyword, also. You should be able
to find (and use) it by selecting Function from the Insert menu in Excel,
then selecting the category User Defined.

Your revised formula doesn't return "" if A3 is empty.

Regards,

Hutch

"gardenhead" wrote:

Hi Tom,

I caught the typo before, no worries. I'm still unable to get the
function to work however. Column G is where I want the comments to be
made so I enter in =ShtComment(G3) and I get the #NAME error. I had the
function listed underneath my macro module so I believe it was in the
right place to begin with.

Good news is that I came up with another solution. Bad news, of course,
is that it poses a new problem. I decided to do away with the first IF
statement that checked the A3 cell for a blank and combine it with the
next one with an AND function so it currently looks like this:

=IF(AND(NOT(ISBLANK(A3)),COUNTIF(S$3:S$20000,A3)1 ), "Duplicate or
secondary invoice in GP", IF(AND(ISERROR(H3),J3="Yes"),"Scheduled to
pay/apply",IF(J3="Yes","Paid/Applied",IF(K3="Yes","Dropship import
error", IF(L3="Yes", "Duplicate or secondary invoice in VNet",
IF(ISTEXT(E3),"Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Good",
"Open")))))))

Works good so far, but now I just need to figure out how to highlight
and delete all the blank cells in the E column for it to work
perfectly. There seems to be a lot of advice already in eliminating
blank cells so that shouldn't be too much trouble.

Thanks again for the help, I'm planning a crash course in VBA and I
hope to come back to that function and make it work.


Tom Hutchins wrote:
ARGGHH! Typo time. The function call should be:

=ShtComment(A3)

Regarding splitting the formula across cells in 2 columns: once you are
satisfied you are getting the expected results, you could hide the column
with the second cell.