Counter Reset

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365
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
ABC
1SequenceTimeTime
2515:0015:01
3615:0615:20
4716:0016:10
5816:2516:45
6917:0017:10
7217:4518:00
8318:3018:45
9419:0019:52
10519:3020:59
11620:0022:06
12720:3023:13
13821:0000:20
14121:0000:20
Sheet1
Cell Formulas
RangeFormula
A3:A5,A7:A14A3=IF(B3="","",MOD(LOOKUP(99,A$1:A2),8)+1)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
If you have the LET function try
+Fluff 1.xlsm
ABC
1SequenceTimeTime
2515:00:0015:01:00
3615:06:0015:20:00
4716:00:0016:10:00
5816:25:0016:45:00
6917:00:0017:10:00
7117:45:0018:00:00
8218:30:0018:45:00
9319:00:0019:52:00
10419:30:0020:59:00
11520:00:0022:06:00
12620:30:0023:13:00
13721:00:0000:20:00
14821:00:0000:20:00
15121:55:0001:01:00
Main
Cell Formulas
RangeFormula
A3:A5,A7:A15A3=LET(N,LOOKUP(99,A$1:A2),IF(B3="","",IF(N=9,1,MOD(N,8)+1)))
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365
Hi Fluff

Sorry but I don't have the LET function.

Is there a workaround available ?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
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
ABC
1SequenceTimeTime
2515:0015:01
3615:0615:20
4716:0016:10
5816:2516:45
6117:0017:10
7217:4518:00
8318:3018:45
9419:0019:52
10519:3020:59
11620:0022:06
12720:3023:13
13821:000:20
14121:000:20
Sheet821
Cell Formulas
RangeFormula
A3:A14A3=IF(B3="","",IF(A2>8,1,MOD(LOOKUP(99,A$1:A2),8)+1))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABC
1SequenceTimeTime
2515:0015:01
3615:0615:20
4716:0016:10
5816:2516:45
6117:0017:10
7217:4518:00
8318:3018:45
9419:0019:52
10519:3020:59
11620:0022:06
12720:3023:13
13821:000:20
14121:000:20
Sheet821
Cell Formulas
RangeFormula
A3:A14A3=IF(B3="","",IF(A2>=8,1,A2+1))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
Actually, ignore my Post #5 above, forgot that A column could have Blanks.

But my Post #4 formula will work.
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365

ADVERTISEMENT

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
ABC
1SequenceTimeTime
2515:0015:01
3615:0615:20
4 16:10
5116:2516:45
6917:0017:10
7117:4518:00
8218:3018:45
9319:0019:52
10419:3020:59
11520:0022:06
12620:3023:13
13721:0000:20
14821:0000:20
Sheet1 (2)
Cell Formulas
RangeFormula
A3:A5,A7:A14A3=IF(B3="","",IF(A2>8,1,MOD(LOOKUP(99,A$1:A2),8)+1))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
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
ABC
1SequenceTimeTime
2515:0015:01
3615:0615:20
4716:0016:10
5816:2516:45
6117:0017:10
7217:4518:00
8318:3018:45
9419:0019:52
10519:3020:59
11620:0022:06
12720:3023:13
13821:000:20
14121:000:20
Sheet821
Cell Formulas
RangeFormula
A3:A14A3=IF(B3="","",IF(LOOKUP(99,A$1:A2)>=8,1,LOOKUP(99,A$1:A2)+1))
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365
Thank you very much jtakw.

This works really well now - perfect !!!!

Have a nice week.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top