CODEWITHSUNDEEP
excel
yeungcase
yeungcase

Reputation: 393

How to automatically fill down by multiple?

I have a column of data that need to be filled. The formula should go like:

SUM(A10 + B10)
SUM A20 + B20
SUM A30 + B30
     .     .
     .     .

I have no idea on how to do the setup. Appreciate for any help :)

Upvotes: 0

Views: 46

Answers (4)

Mitch
Mitch

Reputation: 583

I would probably do something like this, only because I prefer VBA.

Sub FillSheet()
Dim j, k
j = 10
k = 1
  For j = 10 to 500 Step 10   '<<--Starts at 10, then 20, 30, etc up to 500
    Worksheets("YourWorkSheetName").Range("A" & k).Formula = "=SUM(A" & j & ":B" & j & ")"
    k = k +1
  Next j
End Sub

Modify according to your requirements. Change "A" if want the formula in another column. Change "500" to however many lines you need.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

One option is to use INDEX function here, it's not volatile like INDIRECT and will still work if you insert rows or columns, e.g. in cell C2 use this formula copied down

=SUM(INDEX(A$1:B$1000,ROWS(C$2:C2)*10,0))

.....or alternatively, this method will actually give you the formula =SUM(A10,B10) in the first cell and =SUM(A20,B20) in the next cell etc.

Put this formula in C2 and copy down as far as required

="=SUM(A"&ROWS(C$2:C2)*10&",B"&ROWS(C$2:C2)*10&")"

Select whole range > Right Click > Copy > Right Click > Paste Special > Values > OK > ENTER

That creates text versions of the required formulas - to convert to actual formulas do an "Edit/Replace" and replace = with =

Upvotes: 2

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

You're looking for the INDIRECT worksheet function. You need to nest it within the SUM function and you'll get what you're after. E.g. assuming you're in a cell in the very first row on a worksheet, you type:

=SUM(INDIRECT("A" & ROW()*10), INDIRECT("B" & ROW()*10))

Upvotes: 2

Rohit Singh
Rohit Singh

Reputation: 1

Use =SUM(A10,B10) in the first cell and the drag the cell content to all the below cells if you want to fix a attribute like column number than put a $ symbol in front of it eg =SUM($A10,$B10). Similarly, for rows use =SUM(A$10,B$10).

Upvotes: 0

Related Questions