Home |
Search |
Today's Posts |
#1
|
|||
|
|||
consecutive numbers
Hi,
I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#2
|
|||
|
|||
Hi Monique
Format your cells Custom=000 Enter in cell A2 =IF((A1+1)999,A1+1-999,A1+1) This will work until you get to 1999 at which point you would either need to type in a new starting value, or amend the formula to subtract 1999 -- Regards Roger Govier "Monique" wrote in message ... Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#3
|
|||
|
|||
How are you using the range?
In using a cell per number you can have a formula as so: =IF(A1=999,1,A1+1) Simply have the first cell as the value 1 and all the relevant cells number formatted as 000 If I've mis-understood your use, could you give more info on how you use the range please? Basil "Monique" wrote: Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#4
|
|||
|
|||
Forgot to say, the formula I quoted would be put in cell A2 and fill down.
"Basil" wrote: How are you using the range? In using a cell per number you can have a formula as so: =IF(A1=999,1,A1+1) Simply have the first cell as the value 1 and all the relevant cells number formatted as 000 If I've mis-understood your use, could you give more info on how you use the range please? Basil "Monique" wrote: Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#5
|
|||
|
|||
If you put this in A1, and copy down, you will get a sequence 1-5, then
1-5...etc =MOD(ROW()-1,5)+1 Change the 5 to your last desired number (ie 999). HTH -- Dana DeLouis Win XP & Office 2003 "Monique" wrote in message ... Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#6
|
|||
|
|||
I should have added that these numbers are listed from A1-22 and will
continue at C1-22 and E1-22. At any given point the cell may reach 999 but usually not in the same day or the same cell. Therefore, I need to format all of them to meet my condition of 1-999. The formula works, but I don't know how to continue it in cell C1. I originally created the formula for the number as =A1+1 in the second cell, A2. A1 C1(A22+1 entered) =A1+1 C2 (C1+1 entered) A2+1 to C22 to A22 I am not sure how to put both formulas together. Thanks for your help, as I am new at this. Monique "Basil" wrote: How are you using the range? In using a cell per number you can have a formula as so: =IF(A1=999,1,A1+1) Simply have the first cell as the value 1 and all the relevant cells number formatted as 000 If I've mis-understood your use, could you give more info on how you use the range please? Basil "Monique" wrote: Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#7
|
|||
|
|||
This answers my question partially. I have replied to Basil and would like
your input to the other half of the question. I am new at this and left out one of the considerations. Thanks for your help! "Roger Govier" wrote: Hi Monique Format your cells Custom=000 Enter in cell A2 =IF((A1+1)999,A1+1-999,A1+1) This will work until you get to 1999 at which point you would either need to type in a new starting value, or amend the formula to subtract 1999 -- Regards Roger Govier "Monique" wrote in message ... Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#8
|
|||
|
|||
Hi Monique
Using Basil's formula (since it is shorter than mine and achieves the same objective), do the following. 1. Format cell A1:A22, C1:C22 and E1:E22 as Custom=000 2. In cell A2 enter =IF(A1=999,1,A1+1) 3. Copy this formula down through A3:A22 when it will change to =IF(A2=999,1,A2+1) etc. 4. In cell C1 enter the formula =IF(A22=999,1,A22+1) 5. In cell C2 enter =IF(C1=999,1,C1+1) 6. Copy this formula down through cells C3:C22 7. In cell E1 enter the formula =IF(C22=999,1,C22+1) 8. In cell E2 enter =IF(E1=999,1,E1+1) 9. Copy this formula down through E3:E22 The easisest way to copy formulae is to grab the fill handle (little black cross at bottom right of cell) and whilst holding down your left mouse button, drag down across the range of cells you wish to copy to, then release the mouse button. Hope this helps -- Regards Roger Govier "Monique" wrote in message ... Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
#9
|
|||
|
|||
Yes, this did the trick! I hope it was as easy for you as you made it for me!
Thanks to all that brainstormed together! Monique "Roger Govier" wrote: Hi Monique Using Basil's formula (since it is shorter than mine and achieves the same objective), do the following. 1. Format cell A1:A22, C1:C22 and E1:E22 as Custom=000 2. In cell A2 enter =IF(A1=999,1,A1+1) 3. Copy this formula down through A3:A22 when it will change to =IF(A2=999,1,A2+1) etc. 4. In cell C1 enter the formula =IF(A22=999,1,A22+1) 5. In cell C2 enter =IF(C1=999,1,C1+1) 6. Copy this formula down through cells C3:C22 7. In cell E1 enter the formula =IF(C22=999,1,C22+1) 8. In cell E2 enter =IF(E1=999,1,E1+1) 9. Copy this formula down through E3:E22 The easisest way to copy formulae is to grab the fill handle (little black cross at bottom right of cell) and whilst holding down your left mouse button, drag down across the range of cells you wish to copy to, then release the mouse button. Hope this helps -- Regards Roger Govier "Monique" wrote in message ... Hi, I am looking for a formula that has a consecutive number range starting from 001 and ending at 999. I do not use all of them in one day. My goal is to be able to revert back to 001 when the last number of my sequence (999) is reached automatically. This is for an excel spreadsheet that does not expand beyond 999. Thank you for your help! Monique |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Consecutive Numbers in a Row | Excel Worksheet Functions | |||
How do I add consecutive numbers but start over at break in the. | Excel Discussion (Misc queries) | |||
How do I add consecutive numbers but start over at a break in the. | Excel Discussion (Misc queries) | |||
How do I add consecutive numbers but start over at a break in the. | Excel Discussion (Misc queries) | |||
Generate consecutive numbers | Excel Worksheet Functions |