Ask Your Question

Increment by 6 when I pull down cell? [closed]

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-27 12:18:35.457231

1 Answer

Sort by » oldest newest most voted

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

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 +0100 )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 +0100 )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 +0100 )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 +0100 )edit

Question Tools

1 follower


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

Seen: 565 times

Last updated: Mar 09 '17