Count up a value and then reset

[MANICX100]

Hi there,

I am banging my head against a wall trying to have a formula that would instantly count from 1 to 100 and then reset infinitely.

I assumed I could do 1 to 100 twice and then use the fill handle but that causes excel to assume I want 1-100, 1-100, 200-300, 1-100 etc

Now =SEQUENCE(1,100) isn't helpful as that is an array in a single cell

any ideas?

 

[Fluff]

Hi & welcome to MrExcel.
It is not very clear to me as to what you are trying to do. Can you please re-explain, especially this part "instantly count from 1 to 100 and then reset infinitely"

 

[MANICX100]

Sorry the instantly part was confusing.
Essentially can I have a formula which when I use the fill handle will evaluate to
1
2
3
4
etc
up to 100
then reset

so
1
2
3
4
etc
up to 100

ad infinitum

 

[Fluff]

You could use something like this. (I've re-set at 10 to limit the data)

++Fluff.xlsm
	J
1
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	1
13	2
14	3
15	4
16	5
17	6
18	7
19	8
20	9
21	10
22	1
23	2
24	3
25	4
26	5
27	6
28	7
29	8
30	9
31	10
Data

Cell Formulas
Range	Formula
J2:J31	J2	=MOD(SEQUENCE(30,,0),10)+1
Dynamic array formulas.

 

[MANICX100]

Perfect so in my case the formula that is working is

=MOD(SEQUENCE( {how many cells to autopopulate} ,,0),100)+1

Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.