I'm trying to work out how to AutoFill the following data into Google Sheets.
| =A1 |
| =B1 |
| =C1 |
| =A2 |
| =B2 |
| =C2 |
| =A3 |
| =B3 |
| =C3 |
Any help much appreciated!
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityModular arithmetic helps, together with the offset
function:
=offset($A$1, (row(A1)-1)/3, mod(row(A1)-1, 3))
As written above, this formula is equivalent to =A1
because both offset values are 0. But when it's copied down the column, it becomes
=offset($A$1, (row(A2)-1)/3, mod(row(A2)-1, 3))
which evaluates to =offset($A$1, 1/3, 1)
, equivalent to =B2
, because the offset is by 0 rows and 1 columns (fractional offsets are truncated to integer). Two rows down we get
=offset($A$1, (row(A4)-1)/3, mod(row(A4)-1, 3))
which is =A2
because the offset is by 1 row and 0 columns.
In the, perhaps unlikely, event that you want what you show you might copy down from Row1:
="="&char(65+mod(row()-1,3))&int((2+row())/3)
select the relevant column, Ctrl+V, Ctrl+Shift+V, then replace =
with =
.
This will extend the A,B,C series as A,B,C and the numeric to 4s, 5s, 6s ...