Re: [libreoffice-users] Calc - Auto fill stepped cell reference

At 02:03 26/07/2014 -0700, Graham Lovatt wrote:
> 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

At 02:35 26/07/2014 -0700, Graham Lovatt wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted