Formula help

[Donai]

Hi, i need a formula that will look for blanks in Col A6:A, if blank found then return error else ok

Excel Workbook
	A	B
3	JP Morgan	Test
4	JP Morgan	Test
5	JP Morgan	Test
6	JP Morgan	Test
7	JP Morgan	Test
8	JP Morgan	Test
9	JP Morgan	Test
10	JP Morgan	Test
11	JP Morgan	Test
12	JP Morgan	Test
13	JP Morgan	Test
14	JP Morgan	Test
15	JP Morgan	Test
16		Test
17	JP Morgan	Test
18	JP Morgan	Test
19	JP Morgan	Test
20	JP Morgan	Test
21	JP Morgan	Test
22	JP Morgan	Test
23	JP Morgan	Test
24		Test
Sheet2

 

[energman58]

You would need to create a dynamic named range and use that in your formula, you can put a formula in the name manager to achieve this but it depends what you are looking for.

This link has some suggestions about finding the last used cell for a dynamic range where there may be blanks in teh data

http://www.excelforum.com/excel-general/671660-best-way-to-name-dynamic-ranges.html

If you are looking for text and not numbers you need to replace teh big number with a "last word" you can use "zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz" or Rept(z,200)

So your dynamic range formula (in teh name manager) would be something like

DynamicRange

Refers to

=OFFSET(Sheet1!$A$1,0,0,MATCH("zzzzzzzzzz",Sheet1!$A:$A,1))

And your error checking formula would be:

=IF(COUNTBLANK(DynamicRange)>0,"ERROR","OK")


I tend not to use dynamic ranges as they are a pain to debug but I think this is teh only way with your problem

 

[energman58]

Curses - Aladin's solution avoids dynamic named ranges so is a lot nicer than mine. Not thinking this morning - sorry!

 

[Donai]

=IF(COUNTBLANK(A6:INDEX(A:A,MATCH(REPT("z",255),A:A))),"Error","Ok")

The range is assumed to house text values...

Aladin this eliminates the dynamic range, would the same work for numericals? By using Lookup 9.99etc

 

[energman58]

Yes - use a BIG number like

9.99999999999999E+307

 

[Aladin Akyurek]

Aladin this eliminates the dynamic range, would the same work for numericals? By using Lookup 9.99etc

It's in fact a dynamic range, but one without a name assigned to it.

For a numeric range, replace REPT("z",255) with 9.99999999999999E+307, as energman58 also notes.

 

[Katy Jordan]

It's in fact a dynamic range, but one without a name assigned to it.

For a numeric range, replace REPT("z",255) with 9.99999999999999E+307, as energman58 also notes.

So whats the purpose of having a named range?

 

[Aladin Akyurek]

So whats the purpose of having a named range?

If you invoke many formulas in many places in a workook, which must reference a dynamic specification like this:

Sheet1!$A$6:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

it would be easier to assign to this bit a name (say Invoices) and use that name everywhere where that reference is needed.

 

[sudeep]

Use this:

=IF(B2="","error","ok")

If I got your query correct, it will resolve your problem.

 

[jasonb75]

From your original example it appears that you're trying to compare column A to column B.

Excel 07 or newer only.

=COUNTIFS(A:A,"=",B:B,"<>")