How to AUTO 'drag and fill formula' ?

Posted by Ian on September 18, 2001 6:46 AM

I link a formula in Sheet2 to Sheet1 such that when I type a value in Sheet1 cell A1, the formula in Sheet2 cell A1 would show the result, now I want the formula in sheet2 cell A2 to AUTO 'drag and fill formula' itself after I type in a value in sheet1 cell A2, without using the mouse to 'drag and fill formula' in sheet2, How can I do this?

Please send me an excel formula example, Thanks

Posted by Robb on September 24, 2001 3:36 AM

Ian

I think you'll need to use code in Sheet1. Try this, it should complete
the formulae down columnA

- Right click on the page tab for Sheet1
- Select View Code
- Paste this code in the code window

It will copy any formula from A1 in Sheet2 to A2 if you enter something
in Sheet1 A2 and so on down the column.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Columns(1)) Is Nothing Then
ad = Target.Address
If ad = "$A$1" Then GoTo skip
Worksheets("Sheet2").Range(ad).Offset(-1, 0).Copy Destination:=Worksheets("Sheet2").Range(ad)
Else
End If
End With
skip:
End Sub

Any help?

Regards