none
VB: Need UDF for FIND Character(s) in range of cells: SUMPRODUCT ISNUMBER FIND Answer: VB for equiv of COUNTIF() RRS feed

  • Question

  • UPDATE:  with Terry's help, there is an updated answer i will post right here.  the other versions below would work, but (think problem is) after a time the cells would not update to new values unless "maybe" would change after hit enter on them..  IE:  USE THESE 2 ITEMS, THANKS:


    Function CIFA(ByRef rng As range) As Boolean   'embedded array test range of cells  as: if(testa(A9))1,0)
        Dim str As String, rtnRng As range, i As Long: On Error Resume Next: CIFA = False: Dim strArray: strArray = Array("0", "", "N/A")  '"0" or 0
        
        For i = LBound(strArray) To UBound(strArray)
          str = strArray(i): Set rtnRng = rng.find(what:=str, LookAt:=xlWhole)    'xlPart or xlWhole
          If Not rtnRng Is Nothing Then CIFA = True: Exit Function
        Next i
    End Function
     
    Function CIFB(ByRef rng As range, strArray) As Boolean   'external array test range of cells   as: if(testa(A9,{0,"","n/a"})1,0)  if(testa(A9,"0")1,0)
        Dim str As String, rtnRng As range, i As Long: On Error Resume Next: CIFB = False: 'Dim strArray: strArray = Array("0", "", "N/A")
        
        If TypeName(strArray) = "String" Then     'as a single string like:  "XYZ"    << typename  https://msdn.microsoft.com/en-us/library/5422sfdf(v=vs.90).aspx
          Set rtnRng = rng.find(what:=strArray, LookAt:=xlWhole)
          If Not rtnRng Is Nothing Then CIFB = True: Exit Function
        Else
        
        For i = LBound(strArray) To UBound(strArray)
          str = strArray(i): Set rtnRng = rng.find(what:=str, LookAt:=xlWhole)    'xlPart or xlWhole
          If Not rtnRng Is Nothing Then CIFB = True: Exit Function
        Next i
        End If
    End Function

     

    ==========     2 ANSWERS ABOVE  ^^^  ^^

    UPDATE:  pardon not gotten back yet.  running into a few things (irreg hours, eom, running a bit slow), catching up..  i have included an example of what using at the bottom of this 'top' post.  any ideas to help fix 3 items are still needed.  i can make a separate post.  thanks.

    this vb seems to be more in line with the function:  =COUNTIF()    there are 2 tests - variations that do not resolve the same as what would get for Countif()  to:  - test for a zero "0" or 0, or for a type of text test eg's: 

      =COUNTIF(V9:W9,{0;"";"n/a"})    Need function to also cover these items.

      =COUNTIF(F9:H9,"<0.0001")

    NOTE:  should i make a separate post for this?:   thanks.  WHAT NEED

    Function sumFIND(rng As RANGE, strArray)    'vb,  might be more like what need.
        application.SumProduct (--application.IsNumber(application.find(rng, strArray)))

    ----------

    hi,  i have an example of an item i use heavily in my workbook for finding values in cells in a row.  since i end up trying to test many items - variations i have a problem with the bulk it creates.  i use example:

        'IF(SUMPRODUCT(--ISNUMBER(FIND("Z",C9:m9)))>0,1,0)
        'IF(SUMPRODUCT(--ISNUMBER(FIND({"X","Y","Z"},C9:m9)))>0,1,0)
        'IF(SUMPRODUCT(--ISNUMBER(FIND(XYZ,C9:m9)))>0,1,0)    named range XYZ has:  ={"X";"Y";"Z"}

    i have been looking at many variations for countif etc.  but the examples above are pretty much what needed,  for:  - find any number of characters in a range of cells, that can have single character -OR- multiple characters in the cell.  X Y Z above can be whole words anything eg:  AB  DQ  ZFH DEQ P9 D9.

    the examples above allow for a range of cells & the use for the searched items to be placed in a defined name parameter, for a list of items to test for as above, eg:  named range XYZ has:  ={"X";"Y";"Z"}

    pardon i have done a lot of looking into this matter, over a great amount of time..  ehehh yep, and have come up with the following example so far:

    Function sumFIND()

        msgbox "none yet" & space(10), vbquestion

    end function

    if not only need to simply test for characters per se, if there is any other test for the following.. i think the solution would answer for trying to "null" out the use of a formula containing:  CHOOSE ( MATCH  as well (unless someone has a better way of doing that ????),  eg:

    as above:  if(sumproduct(isnumber(find... too much,  then:

    IF(D9>0,CHOOSE(MATCH(D9,RST,0),7,6,5,4),

    if d9>0 does not quite stop an error from happening if there is "no" XYZ or RST etc..  i hope making a UDF to do this wouldn't be too much trouble,  but think most might find it useful.  guessing it may have 3 levels?  for:  SUMPRODUCT, ISNUMBER, FIND?  "--",  thanks in advance.

    ============

    i don't remember how much of this i did, but cannot get it working.  might test a range.

    'not working..
    Function sumP(ParamArray Refs()) As Boolean   'my sumprod with defined name attempt for:  =IF(SUMPRODUCT(--ISNUMBER(FIND(Nc,B9:D9)))>0,1,0)
        Dim i As Integer, c As RANGE
        On Error Resume Next
        For i = LBound(Refs) To UBound(Refs)
          For Each c In Refs(i).Cells
            If c.Value <> 0 Then    'If Not IsEmpty(C.Value) Or C.Value <> 0 Then
              sumP = True
              Exit Function
            End If
          Next
        Next
        'If Selection.Value Like "*Corp*" Then      'WC  orig
    End Function

    ==========

    the only thing using now to null? or test a function is: 

    Function isTXT(ByRef x As RANGE) As Boolean
        isTXT = False
        If application.WorksheetFunction.isTEXT(x.Value) And x.Value <> vbNullString And x.Value <> " " Then isTXT = True
        'If x.Value > 0 Then isTXT = True
    End Function 

     

     

    ==========    OLD ANSWER:   similar to:  COUNTIF  (SEE NEW ANSWERS AT TOP: above)

    note:  what might be more desirable, is have the range first, and string second,  as well as when changing from using a Defined name for the string, to have both samples to be separated by the same character.  guessing a semicolon would be easier since it is what the defined names, and some functions use.

     

    Function CIF(rng As RANGE, strArray)      'call as: =CIF(U9:W9,{"X";"Y";"Z})  or  =CIF(F9,"X")
    Dim str As String, strSource As String, cel As RANGE, rtnRng As RANGE, i As Long: CIF = 0: On Error Resume Next
    'trying for =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},CU9:CW9)))>0,1,0)    'this eg is more like a countif op
    'this eg does not get a true for items test for zero, or string as:  =COUNTIF(V9:W9,{0;"";"n/a"})  =COUNTIF(F9:H9,"<0.0001")
        
    If TypeName(strArray) = "String" Then     'as a single string like "C"    << typename, what version?
        Set rtnRng = rng.find(what:=strArray)  'see: https://msdn.microsoft.com/en-us/library/5422sfdf(v=vs.90).aspx
        If Not rtnRng Is Nothing Then          '
          CIF = 1: Exit Function
        End If
        
    Else                                      'as array like {"A";"B"}
        For i = 1 To UBound(strArray)
          str = strArray(i, 1)
          Set rtnRng = rng.find(what:=str)
          If Not rtnRng Is Nothing Then
            CIF = 1: Exit Function
          End If
        Next i
    End If
        
    End Function



    • Edited by Davexx Monday, April 2, 2018 12:55 PM
    Tuesday, June 27, 2017 11:11 AM

Answers

  • Hi Davexx,

    Do you want to check if specific range could find specific string or some strings in an array like {"A";"B";"C"} format? You could try below code.

    Function RtnTest2(strArray, rng As Range)
    RtnTest2 = 0
    Dim str As String
    Dim strSource As String
    Dim cel As Range
    Dim rtnRng As Range
    If TypeName(strArray) = "String" Then
       'strarray is a single string like "C"
       Set rtnRng = rng.Find(what:=strArray)
       If Not rtnRng Is Nothing Then
       RtnTest2 = 1
       Exit Function
       End If
    Else
      'strarray is array like {"A";"B"}
      For i = 1 To UBound(strArray)
       str = strArray(i, 1)
       Set rtnRng = rng.Find(what:=str)
       If Not rtnRng Is Nothing Then
       RtnTest2 = 1
       Exit Function
       End If
      Next i
    End If
    End Function

    Best Regards,

    Terry

    • Marked as answer by Davexx Saturday, September 16, 2017 8:52 PM
    • Unmarked as answer by Davexx Saturday, September 16, 2017 9:03 PM
    • Marked as answer by Davexx Tuesday, September 19, 2017 12:55 PM
    Tuesday, July 4, 2017 10:04 AM

All replies

  • Hi Davexx,

    What do you want to do? Do you want to custom UDF for your needed? I suggest you provide some simply data to show how you want to handle the data.

    Best Regards,

    Terry

    Friday, June 30, 2017 9:31 AM
  • hi,  thanks.  i seem to forget some portion of what needed, pardon.  i would have many examples, but for text usually as either single characters, or if trying to find whole words in a sentence(s)..  yes this would be for a custom udf.  have been needing this example for a long time, done some checking..

    example of cells:  M9 N9 O9:  A O P,  T9:  FDQIIAVWNMGTTBBB

    test:  M9:T9 for any of letters:  "O","MG" for a TRUE result.  the sumproduct formula to use:

    =IF(SUMPRODUCT(--ISNUMBER(FIND({"O","MG"},M9:T9)))>0,1,0)

    if guessing, might have 10 of these in 1 row (as very restricted use because of bulky size),  want to use a bunch more.

    ANSWER to call as might be:

    i would be looking for a same VB use (as the above formula)  for eg as:  =IF(sumFIND({"O","MG"},M9:T9)>0,1,0)

    depending on what end up calling the udf, guesse will save 60% of the space/ be able to use more of them..

    for longer strings, it would be very helpful if can use a "Defined Name" in place of the:  {"O","MG"}  where in a defined name have to use semicolons to separate the characters,  changes to:  {"O";"MG"}

    i've tried many other eg's such as countif, find etc,  but they do not cover the need as above.  if can get help on this will be put to good use.  thanks.


    • Edited by Davexx Saturday, July 1, 2017 12:37 PM
    Saturday, July 1, 2017 12:36 PM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Find characters.
    http://www.mediafire.com/file/f6bq91wwcvtfnff/07_02_17.xlsm

    Also included macro (not UDF) for reference.

    Monday, July 3, 2017 1:42 AM
  • Hi Davexx,

    Do you want to check if specific range could find specific string or some strings in an array like {"A";"B";"C"} format? You could try below code.

    Function RtnTest2(strArray, rng As Range)
    RtnTest2 = 0
    Dim str As String
    Dim strSource As String
    Dim cel As Range
    Dim rtnRng As Range
    If TypeName(strArray) = "String" Then
       'strarray is a single string like "C"
       Set rtnRng = rng.Find(what:=strArray)
       If Not rtnRng Is Nothing Then
       RtnTest2 = 1
       Exit Function
       End If
    Else
      'strarray is array like {"A";"B"}
      For i = 1 To UBound(strArray)
       str = strArray(i, 1)
       Set rtnRng = rng.Find(what:=str)
       If Not rtnRng Is Nothing Then
       RtnTest2 = 1
       Exit Function
       End If
      Next i
    End If
    End Function

    Best Regards,

    Terry

    • Marked as answer by Davexx Saturday, September 16, 2017 8:52 PM
    • Unmarked as answer by Davexx Saturday, September 16, 2017 9:03 PM
    • Marked as answer by Davexx Tuesday, September 19, 2017 12:55 PM
    Tuesday, July 4, 2017 10:04 AM
  • note:  i have yet to put to full use, but think have more than 2 or 3 dozen sum if isnum find refs in 1 line, inlcuding conditional formats.  testing to see if defined names work as a remote reference to conditonal formats (to change variables easily).  checking.

    i cannot get a conditional format to work with the vb below,  example using is:    =sumF(Za,DB9)

    testing for:  "S" in this example,  where defined name:  Za  has:  ={"P";"Q";"S";"U"}

    am i doing something wrong?  it is to replace:

    =SUMPRODUCT(--ISNUMBER(FIND(Za,DB1259)))>0

    ==========

    hi, i was having trouble with making it work.  i am not that quick at vb, but was able to add item:  dim i as long & seems to be working now.  for 3 cells having 1 character each:  A S Q  the next example worked:

    =sumF("S",CU1259:CW1259)

    =sumF({"S";"U"},CU1259:CW1259)  is there a way to get just a comma separator to work, for ease of use?

    pardon the long reply but few things..  up late-intermittent hours, really big work load & trying to update my system -upgrades and can get running pretty slow. and :)  can take me a really long time just to figure out some vb.

    what got to work,  thanks:

    Function sumF(strArray, rng As RANGE)   'orig, for =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},CU9:CW9)))>0,1,0)
    Dim str As String, strSource As String, cel As RANGE, rtnRng As RANGE, i As Long: sumF = 0    'call as: =sumf({"X";"Y";"Z"},U9:W9)  or  =sumf("X",F9)

    If TypeName(strArray) = "String" Then   'as a single string like "C"
       Set rtnRng = rng.find(what:=strArray)
       If Not rtnRng Is Nothing Then
          sumF = 1: Exit Function
       End If

    Else                                    'as array like {"A";"B"}
      For i = 1 To UBound(strArray)
       str = strArray(i, 1)
       Set rtnRng = rng.find(what:=str)
       If Not rtnRng Is Nothing Then
          sumF = 1: Exit Function
       End If
      Next i
    End If

    End Function

    ==========    isZ  isZERO

    if this helps anyone,  i had a major problem with getting excel to recognize the character:  "0".  if i did this correct:

    Function isZ(ByRef X As RANGE) As Boolean    'does cell have: "0",  call as: =IF(isZ(S9),1,0)
        On Error Resume Next: isZ = False: If IsNumeric(X.Value) And X.Value <> vbNullString And X.Value = 0 Then isZ = True   'seems to work
    'RESET FUNCTION CASE:  rename function sameNAMEx add "x", COMPILE (ignore error), REMOVE x, COMPILE, in cell rename function with correct case, done.
    End Function
    'isz() ISZ() iszero() ISZERO()

    ==========    isTXT

    Function isTXT(ByRef X As RANGE) As Boolean   'does cell have text,  call as:  IF(isTXT(DB9),1,0)
        On Error Resume Next: isTXT = False
        If application.WorksheetFunction.isTEXT(X.Value) And X.Value <> vbNullString And X.Value <> " " Then isTXT = True
    End Function
    'istxt() ISTXT()









    • Edited by Davexx Saturday, September 16, 2017 9:41 PM
    Saturday, September 16, 2017 8:37 PM
  • hi,  thanks for the reply.  i have some follow up to your post.  i may be getting better at understanding the problem - writing it, but i was unable to get your vb to work at first.  if correct, i think it was missing an item to:  DIM i as long

    i was not able to check anything past that point.


    what i would like to have help with is getting a shorter function for the following eg, that does everything that it does.  namely:  it will search mulitple cells for a single or multiple character..  within cells that either have single or multiple characters.  ie, find:

    "A" with a range eg:  "M9:O9"   that can have either a single letter in cell, or mulitple letters-words,  eg:  N9: MASDF  for example:

    =IF(SUMPRODUCT(--ISNUMBER(FIND({"A";"YX";"Z4"},M9:O9)))>0,1,0)    or defined name:  AZ that has:  ={"A";"YX";"Z4"}   for

    =IF(SUMPRODUCT(--ISNUMBER(FIND(AZ,M9:O9)))>0,1,0)

     

    to both be replaced by an eg:  for same -  sum FIND..

    =IF(sumF({"A";"YX";"Z4"},M9:O9),1,0)    or:

    =IF(sumF(AZ,M9:O9),1,0)          by using a defined name:  AZ

    =IF(SUMPRODUCT(--ISNUMBER(FIND({"A";"YX";"Z4"},M9:O9)))>0,1,0)    orig,  looking for a UDF that will accomplish all the same.

     

    the following comes up with an error,  so is another example to address:

    =IF(sumF({"bs";"BS"},LEFT(D9,10)),1,0)

    it is probably the case here where there is more than 1 way to code this, so i am not sure how to pose the question.

    i wonder if a more straight forward result would be to use something that combines the actual separate functions for the original example,  again is:

    =IF(SUMPRODUCT(--ISNUMBER(FIND({"A";"YX";"Z4"},M9:O9)))>0,1,0)

     

    i am not that good at vb, but if above works better to solve,  a wild guesse?:

    Function sumF(strArray, rng As RANGE)

        if application.sumproduct(application.isnumber(application.find(strArray,rng)))>0 then true

    end function

     


    ==========    Example,  is there a way to get this to work?  (to include single or multi letters to test, in cells with single or multi letter combinations within the cell;  and to work with defined names).

    'for:  =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},CU9:CW9)))>0,1,0)

     

    Function sumF(strArray, rng As RANGE) As Boolean
        sumF = False: If application.SumProduct(--application.IsNumber(application.find(strArray, rng))) > 0 Then sumF = True
    End Function



    • Edited by Davexx Sunday, September 17, 2017 12:30 PM
    Sunday, September 17, 2017 12:08 PM
  • hi,  thanks for the help.  was just waiting to see if could get some follow up.  your item works for a variation of what looking for:

    to get a udf to work for eg:  =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},U9:W9)))>0,1,0)

    this does not work,  maybe a place to start?    Problem:

    'for:  =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},U9:W9)))>0,1,0)
    Function sumF(rng As RANGE, strArray) As Boolean    'call as:  =IF(sumF(U9:W9,{"X";"Y";"Z"}),1,0)
        sumF = False: On Error Resume Next: If application.SumProduct(--application.IsNumber(application.find(strArray, rng))) > 0 Then sumF = True
    End Function

     

     

    a shorter verstion of countif,  does the same thing / seems to work:

    Function CIF(rng As RANGE, strArray) As Boolean   'for:  =COUNTIF(CU9:CW9,"g")  call as:  =IF(CIF(CU9:CW9,"E"),1,0)
        CIF = False: On Error Resume Next: If application.CountIf(rng, strArray) > 0 Then CIF = True
    End Function

     

    ==========    ANSWER for your example.  i had to modify 2 items  (DIM i as long, and:  on error resume next) & now seems to work.  thanks for the example.

     

    Function CIFa(rng As RANGE, strArray)   '(countif variation?)  call as: =sumf(U9:W9,{"X";"Y";"Z})  or  =sumf(F9,"X")
    Dim str As String, strSource As String, cel As RANGE, rtnRng As RANGE, i As Long: CIFa = 0: On Error Resume Next
    'trying for =IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},CU9:CW9)))>0,1,0)
    'this eg is more like a countif op
        
    If TypeName(strArray) = "String" Then   'as a single string like "C"
       Set rtnRng = rng.find(what:=strArray)
       If Not rtnRng Is Nothing Then
          CIFa = 1: Exit Function
    'sumf() SUMF()
       End If

    Else                                    'as array like {"A";"B"}

      For i = 1 To UBound(strArray)
       str = strArray(i, 1)
       Set rtnRng = rng.find(what:=str)
       If Not rtnRng Is Nothing Then
          CIFa = 1: Exit Function
       End If
      Next i
    End If

    End Function




    • Edited by Davexx Tuesday, September 19, 2017 1:08 PM
    Tuesday, September 19, 2017 1:05 PM
  • hi,  i have been checking this item out more and am still have 1 problem with a false positive for testing a cell to be 0 (zero), and not just have a zero in it.

    Update:  for countif separators, it seems you can use either commas or semi-colons.  i think the vb in this post needs to use semi-colons & so that does not seem to be a problem then:  just using semi-colons.

    have to call this vb a countif operation,  or:  CIF()  net difference reduction in name & file size by 500k (some 40k chars:  4chars x18 =32*1183 rows = 37856).

    PROBLEM:  test:  =IF(CIF(U9:V9,{0;"";"n/a"})),1,0)   test for:  0, nothing or "n/a",  vb gets a false positive on there being a 0 in the cell, and not the total value of a cell for eg:  0.949 is getting a false positive.

     

    i tried using quotes around the zero, no luck.  is ther a way to include something like if isnumeric & > 0 then true ? 

     

    QUESTION: 

    1.  is ther a way to account for zero (to not give a false positive)?   =IF(CIF(U9:V9,{0;"";"n/a"})),1,0)  shows true on a cell with eg:  0.949

    2.  another area that is not getting recognized are (what call them?) text items being tested for.  eg's that fail:

        IF(COUNTIF(A9,"*/*")>0,

        IF(COUNTIF(U9:H9,"<0.0001"),

    code using:  (i placed the objects in the same order as countif, for ease of use, changes)

     


    Function CIF(rng As RANGE, strArray)      'call as: =CIF(U9:W9,{"X";"Y";"Z})  or  =CIF(F9,"X")
    Dim str As String, strSource As String, cel As RANGE, rtnRng As RANGE, i As Long: CIF = 0: On Error Resume Next
        
    'If IsNumeric(X.Value) And X.Value <> vbNullString And X.Value > 0 Then CIF = True   'will not work?  need whole cell 0 not 1 0 in cell for eg: =CIF(U9:V9,{0;"";"n/a"})

    If TypeName(strArray) = "String" Then     'as a single string like "C"  
        Set rtnRng = rng.find(what:=strArray)  '
        If Not rtnRng Is Nothing Then          '
          CIF = 1: Exit Function
        End If
        
    Else                                      'as array like {"A";"B"}

        For i = 1 To UBound(strArray)
        str = strArray(i, 1)
        Set rtnRng = rng.find(what:=str)
        If Not rtnRng Is Nothing Then
          CIF = 1: Exit Function
        End If
        Next i
    End If

    'RESET FUNCTION CASE:  rename function sameNAMEx add "x", debug-COMPILE (ignore error), REMOVE x, COMPILE, in cell rename function with correct case, done.
    End Function    'cif() CIF()








    • Edited by Davexx Friday, September 22, 2017 6:57 AM
    Thursday, September 21, 2017 5:20 PM
  • hi, i have not had a chance to look at that yet, closely.  i am a bit novice and don't know what to do with it.  see no instructions, click here etcetera.

    (1 concern is opening the file a couple of things start happening,  had some 4 alerts for registery changes, system settings & permissions..  is that supposed to happen?)

    the code is like this, but i will try to save file to a new file name to see if can operate wo pc changes.. i will look at it more, but if you have anything that can help.  not sure how to run it.  What excel version is this workbook for?  thanks.

     

    'Option Explicit
    'Option Base 1

    Sub xFndCHECK()
    Dim n As Integer, i As Integer, m As Integer, rr As Integer, cc As Integer, q As Integer
    Dim a As Variant, p As Variant, sa As Variant, san As Integer
    sa = Array("son", "and", "And", "oo"): san = UBound(sa)
       
    With Sheets("Data").ListObjects("Table1")
        rr = .ListRows.Count
        cc = .ListColumns.Count
        ReDim a(rr, cc - 1)
        ReDim p(rr, 1)
        a = .DataBodyRange
        For i = 1 To rr
            p(i, 1) = 0
            For n = 1 To cc - 1
                For m = 1 To san
                    q = IIf(InStr(a(i, n), sa(m)) = 0, 0, 1)
                    p(i, 1) = p(i, 1) + q
                    p(i, 1) = IIf(p(i, 1) = 0, 0, 1)
                Next m
            Next n
        Next i
        .ListColumns("Hit").DataBodyRange = p
    End With
    End Sub


    • Edited by Davexx Thursday, September 21, 2017 5:40 PM
    Thursday, September 21, 2017 5:39 PM
  • hi,  i have updated this post with what have found so far.  the answer supplied is of great help but with further testing i see it does not cover a couple of items.  briefly:  instead if being the equivalent of a SUMPRODUCT that would do all of what looking for, it is actually like a COUNTIF,  but misses 3 items to test for  (as in update in top post),  to test for: 

      0 / "0" 

      or for a string eg:  "<0.0001"

      test for text within other text does not work

    i would suggest that the order be of:  (range, string)   

    and that separators be  "the same" for defined names / here so more easy to change form 1 form to another:  udf to function eg:  CIF()  to  COUNTIF()    it seems the semicolon is more embedded (in defined names),  but i think using commas all around would be much easier.  (When a UDF or Defined name does not work,  need to be able to simply change between them, or just back to normal:  values in the cell:  {"G","X","Z"}    (for 3 general areas:  udf's to functions to defined names)

    i have had to spend much time (once can get to..) changing back-forth to test items.  i still have dozens of the following 2 items trying to get rid of.  thanks.

     

    '=COUNTIF(V9:W9,{0;"";"n/a"})      =COUNTIF(F9:H9,"<0.0001")
    '=IF(SUMPRODUCT(--ISNUMBER(FIND({"X";"Y";"Z"},U9:W9)))>0,1,0)


    • Edited by Davexx Wednesday, October 11, 2017 8:13 PM
    Wednesday, October 11, 2017 7:16 PM