1

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!

2 Answers 2

1

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

0

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

CHAR
MOD
ROW

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.