0

Let’s assume I have eight excel cells (A1:A8) with the following content:

=MID(B1,1,2)
=MID(B1,3,2)
=MID(B1,5,2)
=MID(B1,7,2)
=MID(B1,9,2)
=MID(B1,11,2)
=MID(B1,13,2)
=MID(B1,15,2)

Now I mark it and drag it down to the next eight cells. I would like to get the same eight formulas just with B2 instead of B1, but excel is giving me B9 for all cells because 9 is the row number of the first new cell.

How can I tell excel that I want to have B2 in the auto fill (and B3 for the next eight and so on)? It is to much data in order to write it all by hand.

Thank you

switches
  • 25
  • 4

2 Answers2

2

Put this in A1 and copy down:

=MID(INDEX(B:B,INT((ROW($ZZ1)-1)/8)+1),MOD(ROW($ZZ1)-1,8)*2+1,2)

It will adjust to the next value in column B every 8 rows and will do the 1,3,5,7,...,15 in the start of the MID. No need for any other formula.

enter image description here

Or, since you stated you have Office 365 put this in A1 and it will spill the results:

=MID(INDEX(B:B,INT(SEQUENCE(COUNTA(B:B)*8,,0)/8)+1),MOD(SEQUENCE(COUNTA(B:B)*8,,0),8)*2+1,2)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
1

If you want to keep your original formula, this will involve 4 steps:

  1. Place the following formula in cell A1

    ="=MID(" & "B" & ROUNDUP(ROW(B1)/8, 0) & ", " & MOD(ROW(B1)-1,8) * 2 + 1 & ", 2)"

  2. Copy down as far as needed.

  3. Select whole of column A, Copy, Paste Special > Values

  4. Select whole of Column A, Ctrl+H (Replace), Find what "=", Replace with "=", Replace All.

This will create formulas in Column A is in your question.

Edward
  • 8,028
  • 2
  • 36
  • 43