Circular List?!

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

Say, based on a pre-defined number N in cel A1, I want in column B to
have all numbers be listed from 1 to N?

How is this doable?

Thanks,
Mike
 
Hi,

Say, based on a pre-defined number N in cel A1, I want in column B to
have all numbers be listed from 1 to N?

How is this doable?

Thanks,
Mike
Click to expand...

Forgot one more thing, after numbers are listed in column B from 1 to
N once, I want next to be able to tell how many times I want those
numbers to be repeated?!

So, if N=20, values from 1 to 20 are then listed in column B (from cel
B1 to B20), followed by another list of those numbers from 1 to N
(from B21 to B40)
 
Did you see the answers to your post of Dec 6, 2010 - "Simple Excel Question!" ?
--
Jim Cone
Portland, Oregon USAhttp://www.contextures.com/excel-sort-addin.html
(30 different ways to sort in excel)

"Mike" <[email protected]>
wrote in message






- Show quoted text -
Click to expand...

Hi Jim,

Yes I did and used it thankfully but I have a different scenario now.
I thought there might be something liks in Excel: Circular(2,20) which
will list TWICE numbers fro 1 to 20....

Mike
 
Hello,

Try this in the code of the sheet
(N=20 in cell A1 and REPEAT=2 in cell A2)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I
If Intersect(Range("A1:A2"), Target) Is Nothing Then Exit Sub
Range("B:B") = ""
For I = 0 To [A1] * [A2] - 1
Cells(I + 1, 2) = I Mod [A1] + 1
Next I
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

"Mike" a écrit dans le message de groupe de discussion :
(e-mail address removed)...

Hi,

Say, based on a pre-defined number N in cel A1, I want in column B to
have all numbers be listed from 1 to N?

How is this doable?

Thanks,
Mike
Click to expand...

Forgot one more thing, after numbers are listed in column B from 1 to
N once, I want next to be able to tell how many times I want those
numbers to be repeated?!

So, if N=20, values from 1 to 20 are then listed in column B (from cel
B1 to B20), followed by another list of those numbers from 1 to N
(from B21 to B40)
 
Functions, which implies formulas (since they are needed to execute them)
cannot affect cells other than the cell they are in, so what you are asking
(at least the way you are asking it) cannot be done. Macros can do what you
asked, only the approach is slightly different. If a VB macro solution would
be acceptable, let us know. In the meantime, here is the answer I posted in
your other thread in the other newsgroup, modified to repeat after 20
instead of the 5 you asked about in that other thread. Put this formula in
your first cell...

=MOD(ROW(A1)-1,20)+1

and copy it down as much as needed (in this case, copy it down through 40
cells).

Rick Rothstein (MVP - Excel)




"Mike" wrote in message

Did you see the answers to your post of Dec 6, 2010 - "Simple Excel
Question!" ?
--
Jim Cone
Portland, Oregon USAhttp://www.contextures.com/excel-sort-addin.html
(30 different ways to sort in excel)

"Mike" <[email protected]>
wrote in
message






- Show quoted text -
Click to expand...

Hi Jim,

Yes I did and used it thankfully but I have a different scenario now.
I thought there might be something liks in Excel: Circular(2,20) which
will list TWICE numbers fro 1 to 20....

Mike
 
Sorry,

Prefer this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I
On Error GoTo ERR_001
If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
Application.EnableEvents = False
Range("B:B") = ""
For I = 0 To [A1] * [A2] - 1
Cells(I + 1, 2) = I Mod [A1] + 1
Next I
End If
ERR_001:
Application.EnableEvents = True
End Sub


"Charabeuh" a écrit dans le message de groupe de discussion :
[email protected]...

Hello,

Try this in the code of the sheet
(N=20 in cell A1 and REPEAT=2 in cell A2)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I
If Intersect(Range("A1:A2"), Target) Is Nothing Then Exit Sub
Range("B:B") = ""
For I = 0 To [A1] * [A2] - 1
Cells(I + 1, 2) = I Mod [A1] + 1
Next I
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

"Mike" a écrit dans le message de groupe de discussion :
(e-mail address removed)...

Hi,

Say, based on a pre-defined number N in cel A1, I want in column B to
have all numbers be listed from 1 to N?

How is this doable?

Thanks,
Mike
Click to expand...

Forgot one more thing, after numbers are listed in column B from 1 to
N once, I want next to be able to tell how many times I want those
numbers to be repeated?!

So, if N=20, values from 1 to 20 are then listed in column B (from cel
B1 to B20), followed by another list of those numbers from 1 to N
(from B21 to B40)
 
Hi Jim,

Yes I did and used it thankfully but I have a different scenario now.
I thought there might be something liks in Excel: Circular(2,20) which
will list TWICE numbers fro 1 to 20....
Click to expand...

You are asking Excel to do things it can not normally do.

So you need to give it a bit of help.

Let's say you have two worksheets in your workbook. The first one is
named SPECS and has in cell A1 the number 20 (representing the last
number in the sequence you want to see repeated "N" times). Then, in
cell B1 put the number of times you want to see the sequence repeated.
This is the number that is represented by "N" in the parenthetical
above.

Now, in your other worksheet, let's say called DATA, in cell A1 put:

=IF(ROW()<=(SPECS!$A$1*SPECS!$B$1),MOD(ROW()-1,SPECS!$A$1)+1,"")

Then copy cell A1 down column A to the highest row you want to see
filled in.
 
You are asking Excel to do things it can not normally do.

So you need to give it a bit of help.

Let's say you have two worksheets in your workbook. The first one is
named SPECS and has in cell A1 the number 20 (representing the last
number in the sequence you want to see repeated "N" times). Then, in
cell B1 put the number of times you want to see the sequence repeated.
This is the number that is represented by "N" in the parenthetical
above.

Now, in your other worksheet, let's say called DATA, in cell A1 put:

=IF(ROW()<=(SPECS!$A$1*SPECS!$B$1),MOD(ROW()-1,SPECS!$A$1)+1,"")

Then copy cell A1 down column A to the highest row you want to see
filled in.
Click to expand...

This is really just a generalized extension of Rick's formula.

A middle of the road "solution" would be to extend Rick's formula with
the following:

=IF(ROW()<=(20*5),MOD(ROW()-1,20)+1,"")

and then you could copy that formula down as far as you want. If you
decide you want to change the factors, then change the two 20's and
the one 5 to the appropriate numbers.

The only purpose of this formula extension is to allow you to copy
from cell A1 down to a row which is well beneath where you want your
sequence to stop, yet knowing that the formula will not show a result
for a row which is "out of range."
 
Back
Top