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!

Hi, 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 40Column 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:=OFFSET($A$1,E$6,0)-OFFSET($B$1,E$6,0)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:=OFFSET($A$1,I$6,0)-OFFSET($B$1,I$6,0)This is what it looked like:

E F G H I 6 0 1 2 3 4 7 -1 -6 -8 73 40You 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

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

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

Bryan

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

Hi, 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 Else blnVert = True End If 'get starting point (range) for paste DestInp: 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 Else intRow = n intCol = 0 End If 'move formula rngDest.Offset(intRow, intCol).Formula = rngCell.Formula n = n + 1 Next rngCell Exit Sub 'error handler ErrHnd: Err.Clear End SubPlease note that Macros

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

Regards

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History