Auto number Non null values
 

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!

Auto number Non null values
 

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<""
--
** John C **


"jayceejay" wrote:

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!

Auto number Non null values
 

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" wrote:

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<""
--
** John C **


"jayceejay" wrote:

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!

Auto number Non null values
 

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 :)
--
** John C **

"jayceejay" wrote:

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" wrote:

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<""
--
** John C **


"jayceejay" wrote:

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!

Auto number Non null values
 

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.


--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
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 :)
--
** John C **

"jayceejay" wrote:

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" wrote:

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<""
--
** John C **


"jayceejay" wrote:

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!

Auto number Non null values
 

Thanks for the education!

"John C" wrote:

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 :)
--
** John C **

"jayceejay" wrote:

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" wrote:

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<""
--
** John C **


"jayceejay" wrote:

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!

Auto number Non null values
 

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.

--
Thanks,
Shane Devenshire


"jayceejay" wrote:

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" wrote:

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<""
--
** John C **


"jayceejay" wrote:

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!