Autofill Forumla

Microsoft Excel 2007
December 11, 2009 at 03:42:31
Specs: Windows Vista
Before I start I understand that this problem has occured due to bad design but the amount of time put into the form so far prevents me from turning back!

I currently have a formula as basic as =$a1-$b1, I want to be able to drag this right and get =$a2-$b2 etc etc, I have over 600 rows to do with 12 columns so doing a paste drag down copy paste transpose over 600 times isnt a great solution, any help would be great!

See More: Autofill Forumla

Report •

December 11, 2009 at 05:16:47

You can't do a conventional drag in one direction and make the formulas change in the opposite direction.

Here is a solution using OFFSET()
This is the data I used:

	A	B	C
1	1	2	-1
2	83	89	-6
3	6	14	-8
4	80	7	73
5	67	27	40
Column C has the results of A1-B1 etc, just for comparison with the results of my solution.

In a row above the formulas you want to drag, enter a simple series of numbers starting at zero
For this example I put 0 in cell E6, 1 in cell F6, then dragged them to the right to extend the series.
In the next row I put this formula in cell E7:

Note the $ signs.
Drag this formula right, under the series of numbers 0,1,2,3,etc.
The formula in cell I7 after dragging was:

This is what it looked like:

	E	F	G	H	I
6	0	1	2	3	4
7	-1	-6	-8	73	40

You will have to adjust this to your exact requirements, but the concept should work!

Please note that OFFSET() is a volatile function, which means that it recalculates every time any cell changes, not just the ones OFFSET refers to. This will add overhead especially with a large number of cells containing OFFSET, and may slow down Excel.


Report •

December 11, 2009 at 05:24:39
Assuming your first formula is in C1 and each row contains no empty rows then you simply select cell C1 and place your cursor over the bottom right hand corner of the box around cell C1 so that your cursor turns into + and then double click on it to auto-fill down.


Report •

December 11, 2009 at 05:55:37
thanks for the help

I will use the offset solution this time round but I think the lesson is to plan the sheet better :)

Report •

Related Solutions

December 11, 2009 at 07:06:16

As an alternative, here is a macro that will transpose formulas.

Write you formulas in say a vertical format, so that you can drag and extend them as usual.
Then select the cells with the formulas.
Run the macro.
The macro asks for the first cell for the transposed formulas.
The Macro moves the formulas (If the initial selection was a column of cells, the result is formulas in a row, or if the initial selection was a row of cells, the result is formulas in a column).

Here is the code to place in a standard module:

Option Explicit

Private Sub FormTrans()
Dim rngSource As Range
Dim rngDest As Range
Dim blnVert As Boolean
Dim rngCell As Range
Dim intRow As Integer
Dim intCol As Integer
Dim n As Integer

On Error GoTo ErrHnd

'copy existing selection and test for single Column or Row
Set rngSource = Selection
If rngSource.Rows.Count > 1 And rngSource.Columns.Count > 1 Then
    MsgBox "Selection must be one column wide or one row deep" & vbCrLf _
            & "please select again"
    GoTo ErrHnd
End If

'flag orientation
If rngSource.Columns.Count > 1 Then
    blnVert = False
    blnVert = True
End If

'get starting point (range) for paste
Set rngDest = Application.InputBox(Prompt:="Select first cell for paste", _
                Title:="Formula Transposer", Type:=8)

'Test input range
If rngDest.Cells.Count <> 1 Then
    MsgBox "Select one cell only as start of destination"
    GoTo DestInp
End If

'move formulas
n = 0
For Each rngCell In rngSource
    'calculate offsets (Vertical or Horizontal)
    If blnVert Then
        intRow = 0
        intCol = n
        intRow = n
        intCol = 0
    End If
    'move formula
    rngDest.Offset(intRow, intCol).Formula = rngCell.Formula
    n = n + 1
Next rngCell
Exit Sub

'error handler
End Sub

Please note that Macros cannot be undone.
Always make a backup copy of your workbook before running this macro.

This routine copies the formulas without changing the cell references, and there are no volatile functions, so no additional overhead unlike the OFFSET solution.


Report •

Ask Question