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

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

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

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

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

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

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

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

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