Copy Formula with Cell Pattern

[Daniel_1978]

Is there a way to copy formulas following a pattern established within the formula?

Example:

A1 = C1
A2 = C4
A3= C7

Is there a way to highlight A1-A3 and drag down and have the formula follow the same sequence for C?

[mikekaganski]

You may do it in these steps.

1. Format column A as text.
2. Put =C1 to A1, =C4 to A2.
3. Select A1 and A2 and drag-copy down as needed. (At this stage, you should have all the cells filled with text strings that look like formulas).
4. Format column A as Number (the contents of the cells will not change, and will still be text)
5. Select column A and use Data▸Text to Columns... to convert the data to formulas (just accepting defaults is OK), or use this FAQ.

Alternatively, you could change your formula to include the information about relative position of the target cell - something like

=OFFSET($C$1;(ROW()-1)*3;0)