# Number sequence formula?

##### Board Regular
Hi All,

I am trying to figure out a number sequence formula.

Here's how the sheet will look

A1 = Name
B1 = number sequence (2,4,B (repeat))

However B1 will reference A1 and if it is blank it will not input a sequence number (2,4,B) It will be left blank, but the next line should carry on where the sequence left off.

What would be the best formula to use for this?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Please give the next few entries in the sequence and explain the pattern.

Sequence

A1 = John
A2 = bob
A3 = blank
A4 = Chris
A5 = Jen
A6 = Roger
A7 = Blank
A8 = Trish

B1 = 2
B2 = 4
B3 = (because A3 is blank B3 is blank and the sequence continues with B4)
B4 = B
B5 = 2
B6 = 4
B7 =
B8 = 2

Pattern

The pattern is 2, 4, B and it repeats indefinately

Correction

B8 should be a B and not 2

If so, how come B8 isn't "B" then?

Because B7 is blank (A7 is the ref cell and it is blank) so the next cell would carry on with the pattern 2, 4, B

You posted your correction as I was asking my question. If you read my question carefully, it asking about the sequence you posted, not the correction you posted afterwards.

Try:

=IF(ISBLANK(A1),"",LOOKUP(MOD(COUNTA(A\$1:A1),3),{0,"B";1,2;2,4}))

in B1 copied down.

sorry doing to many things at once... I will give this a try and let you know but thanks in advance regardless of it working or not

Replies
1
Views
347
Replies
7
Views
334
Replies
1
Views
583
Replies
4
Views
87
Replies
2
Views
608

1,196,280
Messages
6,014,446
Members
441,819
Latest member
Blackov

### 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.

### Which adblocker are you using?

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

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