Named Range with Blanks

[dwgnome]

How can I get the Named Ranges marked in blue to work when there are empty cells within those Named Ranges? When all cells in the range are filled the formula works fine

Is there a way to set up the formula to allow blank cells (empty) or create a dynamic range that would accept blank cells?


Code:


=IF(AND((SUMPRODUCT(ISNUMBER(SEARCH(PaintKeyWords,K3040))*1))=0,(SUMPRODUCT(ISNUMBER(SEARCH(ExceptionsP,K3040))*1))=0),"",IF(((SUMPRODUCT(ISNUMBER(SEARCH(ExceptionsP,K3040))*1)>0)*1)>=1,"","x"))
</PRE>
Where: PaintKeyWords = J3047:J3052
ExceptionsP = K3047:K3052

 

[dwgnome]

Bump.

 

[Aladin Akyurek]

Care to explain what the formula is expected to do?

 

[T. Valko]

How can I get the Named Ranges marked in blue to work when there are empty cells within those Named Ranges? When all cells in the range are filled the formula works fine

Is there a way to set up the formula to allow blank cells (empty) or create a dynamic range that would accept blank cells?


Code:


=IF(AND((SUMPRODUCT(ISNUMBER(SEARCH(PaintKeyWords,K3040))*1))=0,(SUMPRODUCT(ISNUMBER(SEARCH(ExceptionsP,K3040))*1))=0),"",IF(((SUMPRODUCT(ISNUMBER(SEARCH(ExceptionsP,K3040))*1)>0)*1)>=1,"","x"))


</PRE>
Where: PaintKeyWords = J3047:J3052
ExceptionsP = K3047:K3052

Since the ranges are small how about posting what's in those cells so we can see what you're trying to do. Also tell us what's in K3040.

Also, what version of Excel are you using?

 

[dwgnome]

Rather than retyping the issue, please see post:

http://www.mrexcel.com/forum/showthread.php?t=572011

I just want to put the keywords in a list and create a named range. This way it is easier to change the keywords and not have to modify the formulas. But since the keywords will change in length, it would be easier to just have a named range with a larger array size so I don't have to think to change the actual range size.

K3040 contains the phrases.

I am using Excel 2003.

 

[Aladin Akyurek]

Rather than retyping the issue, please see post:

http://www.mrexcel.com/forum/showthread.php?t=572011

I just want to put the keywords in a list and create a named range. This way it is easier to change the keywords and not have to modify the formulas. But since the keywords will change in length, it would be easier to just have a named range with a larger array size so I don't have to think to change the actual range size.

K3040 contains the phrases.

I am using Excel 2003.

See post #12 in your original thread for the required definitions of dynamic named ranges and for a formula which would be faster than one you currently have.