Ask Your Question

Increment by 6 when I pull down cell?

asked 2017-03-09 18:47:14 +0200

medicengonzo gravatar image

When I pull a cell down the cells are usually filled with a series of numbers that increase by 1.

1, 2, 3, 4, 5 etc.

I want to pull a cell and get all the other cells filled by an increment of 6

1,7,13 etc.

How do I go about doing so?

In excel I just have to fill the first three cells with the series I want and it fills automatically. Is there any way I can do something like this in Librecalc?

Also, I would like for this to apply to formulas as well.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-03-09 18:54:16 +0200

I enter 1 to A1, 7 to A2, select them both and drag-copy, and it works.

edit flag offensive delete link more


This works for numbers but when I try with cell references it makes no sense.

In cells B1 and B2 I enter the following: =A1 and =A7. Then, when I pull down the handle it gives me:

A1, A7, A3, A9
medicengonzo gravatar imagemedicengonzo ( 2017-03-09 19:02:21 +0200 )edit

It makes sense, just not what you expect. It just copies the formulas as you put them: in first cell "reference to the cell to the left", in second "reference to cell one left and 6 below". So it goes. To make what you want, you need to construct the reference: =OFFSET($A$1;(ROW()-1)*6;0;1;1)

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-09 19:13:40 +0200 )edit

I don't quite follow. This is exactly what I'm trying to do: In column A I have references to cells on another sheet. Sheet1.A1, Sheet1.A7, Sheet1.A13 etc. I want this series to fill automatically on the reference so I don't have to write them manually and I get a reference of every 6th cell in Sheet1.

medicengonzo gravatar imagemedicengonzo ( 2017-03-09 20:02:26 +0200 )edit

I provided the formula to you. You only need to add the sheet name: =OFFSET(Sheet1.$A$1;(ROW()-1)*6;0;1;1)

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-09 20:23:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-09 18:47:14 +0200

Seen: 243 times

Last updated: Mar 09 '17