Calc - Auto fill stepped cell reference

users
#1

Hello and good morning

Calc
Is it possible to auto fill a stepped cell reference ? I've been trying
without result
What i want is in a cell (or two ) a cell reference, to highlight that cell
and drag down
So the first few cells might be
A1
A8
A16

highlighting and dropping would produce
A1
A8
A16
A24
A32
A40
etc
I've got a few columns needing the same stepped cell reference and this
could save me some time

#2

Yes, it is possible but not as a drag action. LibreOffice will not
"automagically" understand the progression from the provided example (which
is a shame really :slight_smile: )

In the case of your example, select cells A2 to A10 (leave 1 in A1 but don't
select it) and then choose Edit, Fill, Series and then specify Direction
Down (default) Series Type Linear (default), Start value 8, Increment 8

Hope this helps,
Pedro

#3

Hello Pedro & thanks for the reply
Slight edit to my original post I missed out the =

I'd tried the Edit, Fill, Series thing and couldn't get it to work, its ok
with just numbers but including the stuff in front stops it from working

#4

Calc: Is it possible to auto fill a stepped cell reference ? I've been trying without result
What i want is in a cell (or two ) a cell reference, to highlight that cell and drag down
So the first few cells might be
A1
A8
A16

Your first interval is seven and your second one eight. Will the next be nine, or what?

highlighting and dropping would produce
A1
A8
A16
A24
A32
A40
etc

That's supposing that Calc can read your mind and guess that the subsequent gaps should all be eight - despite your first pattern example gap being seven.

I've got a few columns needing the same stepped cell reference and this could save me some time

Slight edit to my original post I missed out the =

Er, I wonder where that should have been in your message!

The simplest way would be to construct the formula yourself. Let's suppose you actually want =A8, =A16, =A24, and so on. You could do this using
="A"&ROW()*8
where the ROW() function returns the number of the row containing the formula itself. But that creates a text string that looks like a formula, not a formula as such. You can interpret text as a formula using the INDIRECT() function, so you need something like
=INDIRECT("A"&ROW()*8)
which you can copy or fill down the column.

If you want A1, A9, A17, and so on, you could use
=INDIRECT("A"&ROW()*8-7)

I trust this helps.

Brian Barker

#5

Brian are you trying to spoil my day by pointing out my many failings - I
have numerous friends for that.
Yes i should have checked the original content before posting -- I'll try to
do better in future
:wink:

Thanks for the indirect solution, haven't tried it yet, hope it works - a
bit more learning

#6

IGraham wrote:

Hello and good morning

Calc
Is it possible to auto fill a stepped cell reference ? I've been trying
without result
What i want is in a cell (or two ) a cell reference, to highlight that cell
and drag down
So the first few cells might be
A1
A8
A16

highlighting and dropping would produce
A1
A8
A16
A24
A32
A40
etc
I've got a few columns needing the same stepped cell reference and this
could save me some time

Fill in the first three cells as you explained:
   A1
   A8
   A16
Then select the second and third (i.e. A8 and A16), and drag the handle at the lower right corner of the selection down. Calc fills the range using the same interval as between the selected cells (i.e. 8) - so subsequent cells are filled with "A24", "A32", etc.

It doesn't work if you select all three, as the intervals between the first and second and second and third are different.

Mark.

#7

Oh, no: that's certainly not my intention!

Brian Barker

#8

Hello again

thanks for the effort Mark

Brian, my causticness was 'tongue in cheek' - mostly

your solution, i managed to get something working, not quite as
self-contained as your example but close
I set up a column 8 16 24 32 etc then used =INDIRECT("A"&CD9) - cd9 being
the cell reference of '8' the first value in the column, that copies down
nicely and the 8 16 24 32 etc is reusable

thanks for pointing me in the right direction