|
Automatic Number Formula
Hi,
Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi, Pinmaster, thanks for your response. I tried the formula you suggested
but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi,
Thought you wanted the number in C1.....sorry for the misunderstanding. What you want is not possible with a formula......but quite possible with VBA, unfortunately I can't help you with that but I'm sure someone will soon. Good luck! Jean-Guy "FJ" wrote: Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi, thanks for your response. I guess I'm off to the Excel Programming
boards, then. :) "pinmaster" wrote: Hi, Thought you wanted the number in C1.....sorry for the misunderstanding. What you want is not possible with a formula......but quite possible with VBA, unfortunately I can't help you with that but I'm sure someone will soon. Good luck! Jean-Guy "FJ" wrote: Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
FJ - If you mean the word "Invoice" is in column A (of your row) and you
want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Addendum !! - When you insert new rows you will need to Copy the
Formula in Column C to the new row(s), BUT I think there is under Tools Options a setting which if set might automatically insert the Column C formula for you (I'll look around for it and write back).. HTH To eliminate the word Invoice from the formula, change it to: =IF(A5="Invoice",COUNTIF($A$5:A5,"Invoice"),"") Jim May "JMay" wrote in message : FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Under Tools, Options, the Edit tab there is an option:
Extend data range formats and formulas; Help says: Extend list formats and formulas Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row I have this "Turned-ON", but it doesn't seem to be working as described above. Hummmmmm -- a separate issue - STAY-TUNED FOR MORE !! "JMay" wrote in message : Addendum !! - When you insert new rows you will need to Copy the Formula in Column C to the new row(s), BUT I think there is under Tools Options a setting which if set might automatically insert the Column C formula for you (I'll look around for it and write back).. HTH To eliminate the word Invoice from the formula, change it to: =IF(A5="Invoice",COUNTIF($A$5:A5,"Invoice"),"") Jim May "JMay" wrote in message : FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi, JMay, thank you so much! This is exactly what I needed! :)
"JMay" wrote: Addendum !! - When you insert new rows you will need to Copy the Formula in Column C to the new row(s), BUT I think there is under Tools Options a setting which if set might automatically insert the Column C formula for you (I'll look around for it and write back).. HTH To eliminate the word Invoice from the formula, change it to: =IF(A5="Invoice",COUNTIF($A$5:A5,"Invoice"),"") Jim May "JMay" wrote in message : FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi, JMay, thanks for your response. This formula works great just like your
other one. I just have one question: this spreadsheet is pretty lengthy and the number go pretty high. If I want to format the number after the word "Invoice" to have a comma, so that it would appear as "Invoice 1,000" how would I do this? I know it should be something like &TEXT(A1"#,##0") but I can't seem to get exactly the right syntax entered in the formula. Thanks in advance for any information. :) "JMay" wrote: FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
In Cell C5 try:
=IF(A5="Invoice","Invoice " & TEXT(COUNTIF($A$5:A5,"Invoice"),"#,##0"),"") Thanks for the feedback. Best of Luck to ya.. Jim "FJ" wrote in message : Hi, JMay, thanks for your response. This formula works great just like your other one. I just have one question: this spreadsheet is pretty lengthy and the number go pretty high. If I want to format the number after the word "Invoice" to have a comma, so that it would appear as "Invoice 1,000" how would I do this? I know it should be something like &TEXT(A1"#,##0") but I can't seem to get exactly the right syntax entered in the formula. Thanks in advance for any information. :) "JMay" wrote: FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Automatic Number Formula
Hi, Jim, thank you so much. :) I always get such great information on these
message boards! Thanks again! :) "JMay" wrote: In Cell C5 try: =IF(A5="Invoice","Invoice " & TEXT(COUNTIF($A$5:A5,"Invoice"),"#,##0"),"") Thanks for the feedback. Best of Luck to ya.. Jim "FJ" wrote in message : Hi, JMay, thanks for your response. This formula works great just like your other one. I just have one question: this spreadsheet is pretty lengthy and the number go pretty high. If I want to format the number after the word "Invoice" to have a comma, so that it would appear as "Invoice 1,000" how would I do this? I know it should be something like &TEXT(A1"#,##0") but I can't seem to get exactly the right syntax entered in the formula. Thanks in advance for any information. :) "JMay" wrote: FJ - If you mean the word "Invoice" is in column A (of your row) and you want column C (of the same row) to increment by 1 then, Assuming the below is range A4:C15 Enter into cell C5 and copy down: =IF(A5="Invoice","Invoice " & COUNTIF($A$5:A5,"Invoice"),"") MyHeader1 MyHeader2 MyHeader3 <<Row 4 Invoice Invoice 1 Invoice Invoice 2 Invoice Invoice 3 Invoice Invoice 4 Invoice Invoice 5 Give this a try and write back if problems,, Jim May "FJ" wrote in message : Hi, Pinmaster, thanks for your response. I tried the formula you suggested but instead of numbering each cell with the word "invoice" as 1, 2, 3, etc., it just gave me the total count of cells with the word "invoice". "pinmaster" wrote: Hi, Try: =COUNTIF(A:A,"invoice") HTH Jean-Guy "FJ" wrote: Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
| All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com