Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with null values | Excel Worksheet Functions | |||
auto data source, removing null | Charts and Charting in Excel | |||
Null values need to be replaced | Excel Discussion (Misc queries) | |||
sum if - null values | Excel Discussion (Misc queries) | |||
Auto-prompt/complete (?) function for number values | Excel Discussion (Misc queries) |