Counter Reset

[Steve 1962]

Hi All

I am using a successful formula provided by this wider expert group - works great !!

Just after an enhancement if possible -

The formula is in column A a;nd works fine. It resets to 1 after the count of 8 is reached (cell A14), as long as the adjancent entry in column B is not blank (column B also has a formula in it).

At times however, it is required to overtype the formula and extend to 9 (shown in cell A6). When this happens, I need the next entry to then reset to 1, as long as there is an adjacent entry in column B.

At the moment, cell A7 is working as originally intended and returning a 2 - this is where I want it to reset to 1 (as long as B7 is not blank).

Thanks

Test File.xlsx
	A	B	C
1	Sequence	Time	Time
2	5	15:00	15:01
3	6	15:06	15:20
4	7	16:00	16:10
5	8	16:25	16:45
6	9	17:00	17:10
7	2	17:45	18:00
8	3	18:30	18:45
9	4	19:00	19:52
10	5	19:30	20:59
11	6	20:00	22:06
12	7	20:30	23:13
13	8	21:00	00:20
14	1	21:00	00:20
Sheet1

Cell Formulas
Range	Formula
A3:A5,A7:A14	A3	=IF(B3="","",MOD(LOOKUP(99,A$1:A2),8)+1)

 

[Fluff]

If you have the LET function try

+Fluff 1.xlsm
	A	B	C
1	Sequence	Time	Time
2	5	15:00:00	15:01:00
3	6	15:06:00	15:20:00
4	7	16:00:00	16:10:00
5	8	16:25:00	16:45:00
6	9	17:00:00	17:10:00
7	1	17:45:00	18:00:00
8	2	18:30:00	18:45:00
9	3	19:00:00	19:52:00
10	4	19:30:00	20:59:00
11	5	20:00:00	22:06:00
12	6	20:30:00	23:13:00
13	7	21:00:00	00:20:00
14	8	21:00:00	00:20:00
15	1	21:55:00	01:01:00
Main

Cell Formulas
Range	Formula
A3:A5,A7:A15	A3	=LET(N,LOOKUP(99,A$1:A2),IF(B3="","",IF(N=9,1,MOD(N,8)+1)))

 

[Steve 1962]

Hi Fluff

Sorry but I don't have the LET function.

Is there a workaround available ?

 

[jtakw]

Hi,

Try this.

Change A6 value to 9, the rest of the column will reset to start at 1,
you can change A column value anywhere, as long as it's above 8, the formula will reset.

Book3.xlsx
	A	B	C
1	Sequence	Time	Time
2	5	15:00	15:01
3	6	15:06	15:20
4	7	16:00	16:10
5	8	16:25	16:45
6	1	17:00	17:10
7	2	17:45	18:00
8	3	18:30	18:45
9	4	19:00	19:52
10	5	19:30	20:59
11	6	20:00	22:06
12	7	20:30	23:13
13	8	21:00	0:20
14	1	21:00	0:20
Sheet821

Cell Formulas
Range	Formula
A3:A14	A3	=IF(B3="","",IF(A2>8,1,MOD(LOOKUP(99,A$1:A2),8)+1))

 

[jtakw]

Hi again,

Actually, there's a Much simpler way to achieve what you want, without using your original formula,
Same description as my post above, change A6 value to 9, formula will reset:

Book3.xlsx
	A	B	C
1	Sequence	Time	Time
2	5	15:00	15:01
3	6	15:06	15:20
4	7	16:00	16:10
5	8	16:25	16:45
6	1	17:00	17:10
7	2	17:45	18:00
8	3	18:30	18:45
9	4	19:00	19:52
10	5	19:30	20:59
11	6	20:00	22:06
12	7	20:30	23:13
13	8	21:00	0:20
14	1	21:00	0:20
Sheet821

Cell Formulas
Range	Formula
A3:A14	A3	=IF(B3="","",IF(A2>=8,1,A2+1))

 

[jtakw]

Actually, ignore my Post #5 above, forgot that A column could have Blanks.

But my Post #4 formula will work.

 

[Steve 1962]

Thanks jtakw

Just one issue -

The formula that you suggested works well when resetting after inputting a 9 (I have done this in cell A6). However, if there is a blank in column B (as in cell B4), cell A5 should continue with its count towards the normal max of 8 and should read 7 (following on from cell A3). It has restarted the count and shows 1 instead of 7.

Thanks

Test File.xlsx
	A	B	C
1	Sequence	Time	Time
2	5	15:00	15:01
3	6	15:06	15:20
4			16:10
5	1	16:25	16:45
6	9	17:00	17:10
7	1	17:45	18:00
8	2	18:30	18:45
9	3	19:00	19:52
10	4	19:30	20:59
11	5	20:00	22:06
12	6	20:30	23:13
13	7	21:00	00:20
14	8	21:00	00:20
Sheet1 (2)

Cell Formulas
Range	Formula
A3:A5,A7:A14	A3	=IF(B3="","",IF(A2>8,1,MOD(LOOKUP(99,A$1:A2),8)+1))

 

[jtakw]

Thanks for pointing that out, keep forgetting there may be blanks.

Use this, do the same tests, I knew there was a way to do without the MOD function and reset the counter properly, hope I'm right:

Book3.xlsx
	A	B	C
1	Sequence	Time	Time
2	5	15:00	15:01
3	6	15:06	15:20
4	7	16:00	16:10
5	8	16:25	16:45
6	1	17:00	17:10
7	2	17:45	18:00
8	3	18:30	18:45
9	4	19:00	19:52
10	5	19:30	20:59
11	6	20:00	22:06
12	7	20:30	23:13
13	8	21:00	0:20
14	1	21:00	0:20
Sheet821

Cell Formulas
Range	Formula
A3:A14	A3	=IF(B3="","",IF(LOOKUP(99,A$1:A2)>=8,1,LOOKUP(99,A$1:A2)+1))

 

[Steve 1962]

Thank you very much jtakw.

This works really well now - perfect !!!!

Have a nice week.

 

[jtakw]

You're welcome, thanks for the feedback.