Auto number Non null values

[jayceejay]

Column A contains row numbers ONLY if there is a corresponding value (text)
in column B.
Is there a function that will do this for me? It needs to re-increment when
a row is added in the middle, much like numbered bullets reconfigure. Right
now, the best I can do is apply a row number whether there is a value there
or not. All I want is an incremented counter in column A for any row in
which I enter a text value in column B. I would appreciate ANY help!

 

[John C]

Assuming your valid data starts in row 2, column B.
A2: =IF(B2=validdata,MAX($A$1:$A1)+1,"")
Edit-->Fill-->Down as far as needed. When you insert a row, you need to copy
the formula into the A column of the new row, and it will re-increment. As
far as what makes column B valid data, I have no idea, that is for you to
determine. If it is just ANYTHING in column B makes it valid, then replace
the B2=validdata with B2<>""

 

[jayceejay]

BRILLIANT! I cannot thank you enough! I was struggling with the <>"". I do
have one question, though. In regard to null values, why wont <>null or
IsNotNull work?

 

[John C]

Because they aren't normal excel functions. Unless you have an add-in or a
UDF NULL is not an excel function, and neither ISNOTNULL.

That being said, I only have xl2003, if you have xl2007 and they have
functions in that regard, I wouldn't know

 

[T. Valko]

if you have xl2007 and they have functions in that regard, I wouldn't know

There isn't beyond ISBLANK, COUNTBLANK which are in all versions.

 

[jayceejay]

Thanks for the education!

Because they aren't normal excel functions. Unless you have an add-in or a
UDF NULL is not an excel function, and neither ISNOTNULL.

That being said, I only have xl2003, if you have xl2007 and they have
functions in that regard, I wouldn't know

 

[ShaneDevenshire]

Hi,

Actually, let's make a few corrections here - NULL is in Excel, and you can
test for it, however, it doesn't mean blank cell in mean the intersection of
two ranges don't exist. Also, NULL is a legal, but different thing in VBA.

Back to the spreadsheet, try this formula

=A1:A10 B1:B10

Yes leave a space between them. You will get a NULL error
If the formula is in C1 try this
=ERROR.TYPE(C1)
You will get 1.

Check the help system for ERROR.TYPE and you will see:

If error_val is ERROR.TYPE returns
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
Anything else #N/A

In VBA:

The Null keyword is used as a Variant subtype. It indicates that a variable
contains no valid data.