Number sequence formula?

[adavies]

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?

 

[Andrew Poulsom]

Please give the next few entries in the sequence and explain the pattern.

 

[adavies]

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

 

[adavies]

Pattern

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

 

[adavies]

Correction

B8 should be a B and not 2

 

[GlennUK]

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

 

[adavies]

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

 

[GlennUK]

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.

 

[Andrew Poulsom]

Try:

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

in B1 copied down.

 

[adavies]

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